<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <meta name="generator" content="VuePress 2.0.0-beta.60">
    <script>
      (function() {
        const userMode = localStorage.getItem('vuepress-reco-color-scheme') || 'auto';
        const systemDarkMode = window.matchMedia && window.matchMedia('(prefers-color-scheme: dark)').matches;

        if (userMode === 'dark' || (userMode === 'auto' && systemDarkMode)) {
          document.documentElement.classList.toggle('dark', true);
        }
      })();
    </script>
    <title>MySql 数据库 | 阿派 | Apai Blog</title><meta name="description" content="Just playing around">
    <link rel="modulepreload" href="/assets/app-8a43a0f0.js"><link rel="modulepreload" href="/assets/framework-e1bed10d.js"><link rel="modulepreload" href="/assets/MySql.html-5adc8c86.js"><link rel="modulepreload" href="/assets/MySql.html-4bcdc408.js"><link rel="prefetch" href="/assets/index.html-ef2f4f01.js" as="script"><link rel="prefetch" href="/assets/index.html-1013d654.js" as="script"><link rel="prefetch" href="/assets/index.html-a146dedc.js" as="script"><link rel="prefetch" href="/assets/index.html-f4b2eaea.js" as="script"><link rel="prefetch" href="/assets/index.html-e3ed352e.js" as="script"><link rel="prefetch" href="/assets/index.html-42ce2ab9.js" as="script"><link rel="prefetch" href="/assets/index.html-ec24ec9e.js" as="script"><link rel="prefetch" href="/assets/index.html-d3d8d7ce.js" as="script"><link rel="prefetch" href="/assets/index.html-9faf073b.js" as="script"><link rel="prefetch" href="/assets/index.html-9364b7dd.js" as="script"><link rel="prefetch" href="/assets/index.html-fc0fb462.js" as="script"><link rel="prefetch" href="/assets/index.html-603c6c19.js" as="script"><link rel="prefetch" href="/assets/index.html-0349deb8.js" as="script"><link rel="prefetch" href="/assets/index.html-09405a87.js" as="script"><link rel="prefetch" href="/assets/index.html-91577c59.js" as="script"><link rel="prefetch" href="/assets/index.html-552ae255.js" as="script"><link rel="prefetch" href="/assets/index.html-424369c4.js" as="script"><link rel="prefetch" href="/assets/index.html-1dfc0069.js" as="script"><link rel="prefetch" href="/assets/index.html-ec62f595.js" as="script"><link rel="prefetch" href="/assets/index.html-4621ef22.js" as="script"><link rel="prefetch" href="/assets/index.html-e189ec13.js" as="script"><link rel="prefetch" href="/assets/index.html-b424a45b.js" as="script"><link rel="prefetch" href="/assets/index.html-623952a8.js" as="script"><link rel="prefetch" href="/assets/index.html-cedcb7c8.js" as="script"><link rel="prefetch" href="/assets/index.html-07f61d2f.js" as="script"><link rel="prefetch" href="/assets/index.html-9ef01f1e.js" as="script"><link rel="prefetch" href="/assets/index.html-dfa6d19f.js" as="script"><link rel="prefetch" href="/assets/index.html-23dd0955.js" as="script"><link rel="prefetch" href="/assets/index.html-1e7a4ff3.js" as="script"><link rel="prefetch" href="/assets/index.html-e12f154a.js" as="script"><link rel="prefetch" href="/assets/index.html-30de2d02.js" as="script"><link rel="prefetch" href="/assets/index.html-6a244983.js" as="script"><link rel="prefetch" href="/assets/index.html-8900b10e.js" as="script"><link rel="prefetch" href="/assets/index.html-b3210461.js" as="script"><link rel="prefetch" href="/assets/biaoqian-fenlei-moban.html-2db9524d.js" as="script"><link rel="prefetch" href="/assets/MuBan-Apai.html-5af383c8.js" as="script"><link rel="prefetch" href="/assets/guide.html-5aaed80e.js" as="script"><link rel="prefetch" href="/assets/Lu-aboutMe.html-82ee5401.js" as="script"><link rel="prefetch" href="/assets/kaishi.html-9d8493a0.js" as="script"><link rel="prefetch" href="/assets/Hobby-SheYing.html-3ac28a1b.js" as="script"><link rel="prefetch" href="/assets/Lu-aboutMe.html-96d10c31.js" as="script"><link rel="prefetch" href="/assets/Work-MaYun.html-5e497e8c.js" as="script"><link rel="prefetch" href="/assets/Java-basics.html-4a44925e.js" as="script"><link rel="prefetch" href="/assets/JavaAPI.html-ae8a1843.js" as="script"><link rel="prefetch" href="/assets/JavaBiKeng04.html-c9e75b16.js" as="script"><link rel="prefetch" href="/assets/JavaGongNeng03.html-6ca97faf.js" as="script"><link rel="prefetch" href="/assets/JavaJinJie02.html-eaabb81d.js" as="script"><link rel="prefetch" href="/assets/JavaMyUtil.html-e2cff404.js" as="script"><link rel="prefetch" href="/assets/MyBatisPlus.html-b608a85b.js" as="script"><link rel="prefetch" href="/assets/MySqlFunction.html-23affe6f.js" as="script"><link rel="prefetch" href="/assets/RabbitMQ.html-2cc19b22.js" as="script"><link rel="prefetch" href="/assets/Redis.html-f7a014e9.js" as="script"><link rel="prefetch" href="/assets/ShiWuAffair.html-33f5c0c4.js" as="script"><link rel="prefetch" href="/assets/Spring.html-628746d6.js" as="script"><link rel="prefetch" href="/assets/SpringConfig.html-49f3c621.js" as="script"><link rel="prefetch" href="/assets/SpringPeiZhi.html-ac6df88a.js" as="script"><link rel="prefetch" href="/assets/SpringSecurity.html-057633f1.js" as="script"><link rel="prefetch" href="/assets/WebSocket.html-57d6fc97.js" as="script"><link rel="prefetch" href="/assets/DockerCompose.html-98ad6b52.js" as="script"><link rel="prefetch" href="/assets/Linux.html-43351365.js" as="script"><link rel="prefetch" href="/assets/Nginx.html-f1276de6.js" as="script"><link rel="prefetch" href="/assets/RabbitMQ_COPY.html-5cdb6370.js" as="script"><link rel="prefetch" href="/assets/Redis_COPY.html-486e1d26.js" as="script"><link rel="prefetch" href="/assets/Redis_CRUD.html-47e0af0f.js" as="script"><link rel="prefetch" href="/assets/BatJiaoBen.html-d2d2666a.js" as="script"><link rel="prefetch" href="/assets/DmShengChen.html-bb069d8e.js" as="script"><link rel="prefetch" href="/assets/EasyExcel.html-b1eb613d.js" as="script"><link rel="prefetch" href="/assets/GitLu.html-eae89aa5.js" as="script"><link rel="prefetch" href="/assets/JavaKaiFa.html-4fbb86cc.js" as="script"><link rel="prefetch" href="/assets/JSR303.html-b976b418.js" as="script"><link rel="prefetch" href="/assets/MinIO.html-5e3d4702.js" as="script"><link rel="prefetch" href="/assets/ShardingJDBC.html-d0f811b3.js" as="script"><link rel="prefetch" href="/assets/Swagger.html-bb111a21.js" as="script"><link rel="prefetch" href="/assets/Vuepress.html-994cf672.js" as="script"><link rel="prefetch" href="/assets/Angular.html-3115afab.js" as="script"><link rel="prefetch" href="/assets/ElementUI.html-35bc6477.js" as="script"><link rel="prefetch" href="/assets/Html-Js.html-015f1806.js" as="script"><link rel="prefetch" href="/assets/Vue-basics.html-a64846f4.js" as="script"><link rel="prefetch" href="/assets/Vue3_group.html-f53be77f.js" as="script"><link rel="prefetch" href="/assets/Vue3_option.html-19f76b9e.js" as="script"><link rel="prefetch" href="/assets/ApifoxAPIceshi.html-19cd8153.js" as="script"><link rel="prefetch" href="/assets/PDManeryuanshujianmo.html-f6fa9d9e.js" as="script"><link rel="prefetch" href="/assets/OCRwenzishibie.html-11fc474d.js" as="script"><link rel="prefetch" href="/assets/EasyExcelbiaogeku.html-30777927.js" as="script"><link rel="prefetch" href="/assets/Javadaimashengchengqi.html-cf566ce0.js" as="script"><link rel="prefetch" href="/assets/JDKshuangbanben.html-1f259d3b.js" as="script"><link rel="prefetch" href="/assets/JSR303canshuxiaoyan.html-78322130.js" as="script"><link rel="prefetch" href="/assets/Mail  youjian.html-7e3a0f69.js" as="script"><link rel="prefetch" href="/assets/MyBatisPlusxiangjie.html-0168c152.js" as="script"><link rel="prefetch" href="/assets/MySQLzhucongfuzhi.html-7a4739e8.js" as="script"><link rel="prefetch" href="/assets/Redis-API.html-f488940c.js" as="script"><link rel="prefetch" href="/assets/SpringBootshiwu.html-c07a3864.js" as="script"><link rel="prefetch" href="/assets/SpringBootduxiefenli.html-f0993780.js" as="script"><link rel="prefetch" href="/assets/Springguolvqihelanjieqi.html-78feb321.js" as="script"><link rel="prefetch" href="/assets/WebSocket.html-caecf367.js" as="script"><link rel="prefetch" href="/assets/quanjuyichang-JSR303.html-add01eff.js" as="script"><link rel="prefetch" href="/assets/Docker.html-14746447.js" as="script"><link rel="prefetch" href="/assets/Dockeryuanchengfangwenbushu.html-7dc4f8d3.js" as="script"><link rel="prefetch" href="/assets/Nginxfanxiangdaili.html-42533685.js" as="script"><link rel="prefetch" href="/assets/Apai-SheYingOne.html-143bc574.js" as="script"><link rel="prefetch" href="/assets/Apai-Year.html-b713587f.js" as="script"><link rel="prefetch" href="/assets/My2305.html-b224c5e2.js" as="script"><link rel="prefetch" href="/assets/My2306.html-94838726.js" as="script"><link rel="prefetch" href="/assets/My2307.html-df2118c4.js" as="script"><link rel="prefetch" href="/assets/My2308.html-396c79bc.js" as="script"><link rel="prefetch" href="/assets/My2309.html-1ebdc4bb.js" as="script"><link rel="prefetch" href="/assets/My2310.html-d4ffbd04.js" as="script"><link rel="prefetch" href="/assets/My2311.html-30f23133.js" as="script"><link rel="prefetch" href="/assets/My2312.html-2da62191.js" as="script"><link rel="prefetch" href="/assets/Yi-HuBei.html-90d02bf7.js" as="script"><link rel="prefetch" href="/assets/Yi-ZhuHai.html-e202f6fb.js" as="script"><link rel="prefetch" href="/assets/Apache ECharts tubiao.html-6d4a4703.js" as="script"><link rel="prefetch" href="/assets/Vue3 zuheshi API.html-9fe2887f.js" as="script"><link rel="prefetch" href="/assets/Vue3 xuanxiangshi API.html-d584c181.js" as="script"><link rel="prefetch" href="/assets/Vue3-i18n-guojihuachajian.html-16f6e650.js" as="script"><link rel="prefetch" href="/assets/Batpichulijiaoben.html-307223b0.js" as="script"><link rel="prefetch" href="/assets/Gitgongzuoliu.html-fa6e129f.js" as="script"><link rel="prefetch" href="/assets/Typoratuchuang.html-22c57156.js" as="script"><link rel="prefetch" href="/assets/Vuepress.html-df146bdb.js" as="script"><link rel="prefetch" href="/assets/My2401.html-49f6440f.js" as="script"><link rel="prefetch" href="/assets/My2402.html-950d4e85.js" as="script"><link rel="prefetch" href="/assets/shenhai-0509.html-2007281b.js" as="script"><link rel="prefetch" href="/assets/zhuhaixing.html-17db8380.js" as="script"><link rel="prefetch" href="/assets/404.html-f9875e7b.js" as="script"><link rel="prefetch" href="/assets/index.html-40bced6f.js" as="script"><link rel="prefetch" href="/assets/index.html-3c78780e.js" as="script"><link rel="prefetch" href="/assets/index.html-ef51ba1b.js" as="script"><link rel="prefetch" href="/assets/index.html-bdec80a5.js" as="script"><link rel="prefetch" href="/assets/index.html-3b5427f8.js" as="script"><link rel="prefetch" href="/assets/index.html-cbd49540.js" as="script"><link rel="prefetch" href="/assets/index.html-b0b97c81.js" as="script"><link rel="prefetch" href="/assets/index.html-2793d245.js" as="script"><link rel="prefetch" href="/assets/index.html-71d9a5d9.js" as="script"><link rel="prefetch" href="/assets/index.html-4e7d77cd.js" as="script"><link rel="prefetch" href="/assets/index.html-aeace11a.js" as="script"><link rel="prefetch" href="/assets/index.html-c62ed802.js" as="script"><link rel="prefetch" href="/assets/index.html-e640d31f.js" as="script"><link rel="prefetch" href="/assets/index.html-48e1c605.js" as="script"><link rel="prefetch" href="/assets/index.html-385dcb56.js" as="script"><link rel="prefetch" href="/assets/index.html-1bb1a523.js" as="script"><link rel="prefetch" href="/assets/index.html-1ed80e8d.js" as="script"><link rel="prefetch" href="/assets/index.html-2c154a45.js" as="script"><link rel="prefetch" href="/assets/index.html-3e93d436.js" as="script"><link rel="prefetch" href="/assets/index.html-2a93e2c9.js" as="script"><link rel="prefetch" href="/assets/index.html-cff07ba3.js" as="script"><link rel="prefetch" href="/assets/index.html-7ec32a34.js" as="script"><link rel="prefetch" href="/assets/index.html-ddb40108.js" as="script"><link rel="prefetch" href="/assets/index.html-40ab6c4f.js" as="script"><link rel="prefetch" href="/assets/index.html-47e4ee8b.js" as="script"><link rel="prefetch" href="/assets/index.html-c15250e1.js" as="script"><link rel="prefetch" href="/assets/index.html-fb2ea2e4.js" as="script"><link rel="prefetch" href="/assets/index.html-d7e5408a.js" as="script"><link rel="prefetch" href="/assets/index.html-a38055d0.js" as="script"><link rel="prefetch" href="/assets/index.html-be4e50cb.js" as="script"><link rel="prefetch" href="/assets/index.html-660e4bef.js" as="script"><link rel="prefetch" href="/assets/index.html-98aa61ea.js" as="script"><link rel="prefetch" href="/assets/index.html-a6b7472f.js" as="script"><link rel="prefetch" href="/assets/index.html-ab78917a.js" as="script"><link rel="prefetch" href="/assets/biaoqian-fenlei-moban.html-f2c6c309.js" as="script"><link rel="prefetch" href="/assets/MuBan-Apai.html-5714876d.js" as="script"><link rel="prefetch" href="/assets/guide.html-9a89b22b.js" as="script"><link rel="prefetch" href="/assets/Lu-aboutMe.html-f8203e0a.js" as="script"><link rel="prefetch" href="/assets/kaishi.html-4ff9d0f3.js" as="script"><link rel="prefetch" href="/assets/Hobby-SheYing.html-e1ab7a00.js" as="script"><link rel="prefetch" href="/assets/Lu-aboutMe.html-4b735204.js" as="script"><link rel="prefetch" href="/assets/Work-MaYun.html-a185f330.js" as="script"><link rel="prefetch" href="/assets/Java-basics.html-c0d3a063.js" as="script"><link rel="prefetch" href="/assets/JavaAPI.html-f75cbb54.js" as="script"><link rel="prefetch" href="/assets/JavaBiKeng04.html-d2fc755d.js" as="script"><link rel="prefetch" href="/assets/JavaGongNeng03.html-c51a3060.js" as="script"><link rel="prefetch" href="/assets/JavaJinJie02.html-e5a66660.js" as="script"><link rel="prefetch" href="/assets/JavaMyUtil.html-6e16ee98.js" as="script"><link rel="prefetch" href="/assets/MyBatisPlus.html-1cafeb7e.js" as="script"><link rel="prefetch" href="/assets/MySqlFunction.html-2c432c8c.js" as="script"><link rel="prefetch" href="/assets/RabbitMQ.html-d771200f.js" as="script"><link rel="prefetch" href="/assets/Redis.html-aa5c160a.js" as="script"><link rel="prefetch" href="/assets/ShiWuAffair.html-ce7fc825.js" as="script"><link rel="prefetch" href="/assets/Spring.html-3d622ed7.js" as="script"><link rel="prefetch" href="/assets/SpringConfig.html-6fe667f9.js" as="script"><link rel="prefetch" href="/assets/SpringPeiZhi.html-96b777da.js" as="script"><link rel="prefetch" href="/assets/SpringSecurity.html-fe792e2f.js" as="script"><link rel="prefetch" href="/assets/WebSocket.html-7fd7be86.js" as="script"><link rel="prefetch" href="/assets/DockerCompose.html-f6ef44c4.js" as="script"><link rel="prefetch" href="/assets/Linux.html-a1058183.js" as="script"><link rel="prefetch" href="/assets/Nginx.html-bf694d87.js" as="script"><link rel="prefetch" href="/assets/RabbitMQ_COPY.html-1e53d902.js" as="script"><link rel="prefetch" href="/assets/Redis_COPY.html-ef853ef3.js" as="script"><link rel="prefetch" href="/assets/Redis_CRUD.html-7a4bb7d5.js" as="script"><link rel="prefetch" href="/assets/BatJiaoBen.html-20ec59d7.js" as="script"><link rel="prefetch" href="/assets/DmShengChen.html-92a2db1f.js" as="script"><link rel="prefetch" href="/assets/EasyExcel.html-ac2c3493.js" as="script"><link rel="prefetch" href="/assets/GitLu.html-27823973.js" as="script"><link rel="prefetch" href="/assets/JavaKaiFa.html-ff11bb84.js" as="script"><link rel="prefetch" href="/assets/JSR303.html-0c745945.js" as="script"><link rel="prefetch" href="/assets/MinIO.html-958125dd.js" as="script"><link rel="prefetch" href="/assets/ShardingJDBC.html-ce0afe07.js" as="script"><link rel="prefetch" href="/assets/Swagger.html-9c9fb3be.js" as="script"><link rel="prefetch" href="/assets/Vuepress.html-4e07419a.js" as="script"><link rel="prefetch" href="/assets/Angular.html-3e716bd5.js" as="script"><link rel="prefetch" href="/assets/ElementUI.html-70835a6c.js" as="script"><link rel="prefetch" href="/assets/Html-Js.html-848bb521.js" as="script"><link rel="prefetch" href="/assets/Vue-basics.html-772005d8.js" as="script"><link rel="prefetch" href="/assets/Vue3_group.html-69369e84.js" as="script"><link rel="prefetch" href="/assets/Vue3_option.html-37ac05d4.js" as="script"><link rel="prefetch" href="/assets/ApifoxAPIceshi.html-0bcb1468.js" as="script"><link rel="prefetch" href="/assets/PDManeryuanshujianmo.html-4c377e02.js" as="script"><link rel="prefetch" href="/assets/OCRwenzishibie.html-b4248616.js" as="script"><link rel="prefetch" href="/assets/EasyExcelbiaogeku.html-511b0f0b.js" as="script"><link rel="prefetch" href="/assets/Javadaimashengchengqi.html-33991c95.js" as="script"><link rel="prefetch" href="/assets/JDKshuangbanben.html-8bba14d4.js" as="script"><link rel="prefetch" href="/assets/JSR303canshuxiaoyan.html-7d12c0ef.js" as="script"><link rel="prefetch" href="/assets/Mail  youjian.html-d520132f.js" as="script"><link rel="prefetch" href="/assets/MyBatisPlusxiangjie.html-94d1940a.js" as="script"><link rel="prefetch" href="/assets/MySQLzhucongfuzhi.html-bf156f04.js" as="script"><link rel="prefetch" href="/assets/Redis-API.html-534cd405.js" as="script"><link rel="prefetch" href="/assets/SpringBootshiwu.html-a83407ed.js" as="script"><link rel="prefetch" href="/assets/SpringBootduxiefenli.html-e50eba32.js" as="script"><link rel="prefetch" href="/assets/Springguolvqihelanjieqi.html-b7cdfd68.js" as="script"><link rel="prefetch" href="/assets/WebSocket.html-7c2b5253.js" as="script"><link rel="prefetch" href="/assets/quanjuyichang-JSR303.html-1f70d8b7.js" as="script"><link rel="prefetch" href="/assets/Docker.html-51026828.js" as="script"><link rel="prefetch" href="/assets/Dockeryuanchengfangwenbushu.html-f42be316.js" as="script"><link rel="prefetch" href="/assets/Nginxfanxiangdaili.html-a98dc972.js" as="script"><link rel="prefetch" href="/assets/Apai-SheYingOne.html-b5b7ae7c.js" as="script"><link rel="prefetch" href="/assets/Apai-Year.html-ce260a3f.js" as="script"><link rel="prefetch" href="/assets/My2305.html-8daa1c69.js" as="script"><link rel="prefetch" href="/assets/My2306.html-893e00c1.js" as="script"><link rel="prefetch" href="/assets/My2307.html-e78450ff.js" as="script"><link rel="prefetch" href="/assets/My2308.html-0e38205b.js" as="script"><link rel="prefetch" href="/assets/My2309.html-06a48ebc.js" as="script"><link rel="prefetch" href="/assets/My2310.html-59afa2c3.js" as="script"><link rel="prefetch" href="/assets/My2311.html-b634eab1.js" as="script"><link rel="prefetch" href="/assets/My2312.html-aafaa08a.js" as="script"><link rel="prefetch" href="/assets/Yi-HuBei.html-61c8ef5d.js" as="script"><link rel="prefetch" href="/assets/Yi-ZhuHai.html-26a46f60.js" as="script"><link rel="prefetch" href="/assets/Apache ECharts tubiao.html-e9cc0012.js" as="script"><link rel="prefetch" href="/assets/Vue3 zuheshi API.html-11ae0fbc.js" as="script"><link rel="prefetch" href="/assets/Vue3 xuanxiangshi API.html-ce744458.js" as="script"><link rel="prefetch" href="/assets/Vue3-i18n-guojihuachajian.html-1fa338e8.js" as="script"><link rel="prefetch" href="/assets/Batpichulijiaoben.html-06ab27bc.js" as="script"><link rel="prefetch" href="/assets/Gitgongzuoliu.html-b1e45d5c.js" as="script"><link rel="prefetch" href="/assets/Typoratuchuang.html-cb5c9b25.js" as="script"><link rel="prefetch" href="/assets/Vuepress.html-c36453a2.js" as="script"><link rel="prefetch" href="/assets/My2401.html-9b16cb88.js" as="script"><link rel="prefetch" href="/assets/My2402.html-3ade37a1.js" as="script"><link rel="prefetch" href="/assets/shenhai-0509.html-648d7d95.js" as="script"><link rel="prefetch" href="/assets/zhuhaixing.html-7d486783.js" as="script"><link rel="prefetch" href="/assets/404.html-7c90b0cc.js" as="script"><link rel="prefetch" href="/assets/reco-valine-a0c1af1f.js" as="script">
    <link rel="preload" href="/assets/style-8a0f09c6.css" as="style"><link rel="stylesheet" href="/assets/style-8a0f09c6.css">
  </head>
  <body>
    <div id="app"><!--[--><div class="theme-container"><div class="common-wrapper show-series show-catalog"><div><header class="navbar-container"><!--[--><div class="site-brand nav-item"><img class="logo" src="/logo.png" alt="阿派 | Apai Blog"><a href="/" class="site-name can-hide">阿派 | Apai Blog</a></div><div class="nav-item navbar-links-wrapper" style=""><form class="search-box" role="search"><input type="search" autocomplete="off" spellcheck="false" value><!----></form><nav class="navbar-links"><!--[--><div class="navbar-links__item"><a href="/" class="link router-link-active" aria-label="Home"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Home<!--]--></span></span><!--[--><!--]--></a></div><div class="navbar-links__item"><a href="/categories/Java/1/" class="link" aria-label="分类组"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->分类组<!--]--></span></span><!--[--><!--]--></a></div><div class="navbar-links__item"><a href="/tags/Rests/1/" class="link" aria-label="标签组"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->标签组<!--]--></span></span><!--[--><!--]--></a></div><div class="navbar-links__item"><div class="dropdown-link"><button class="dropdown-link__title" type="button" aria-label="文档组"><span class="xicon-container left title"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->文档组<!--]--></span></span><span class="arrow down"></span></button><button class="dropdown-link--mobile__title" type="button" aria-label="文档组"><span class="title"><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->文档组<!--]--></span></span></span><span class="right arrow"></span></button><ul style="display:none;" class="dropdown-link__container"><!--[--><li class="dropdown-link__item"><a href="/docs/Web-develop/Html-Js" class="link" aria-label="Web-开发"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Web-开发<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Java-develop/JavaMyUtil" class="link" aria-label="Java-开发"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Java-开发<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Linux-develop/Linux" class="link" aria-label="Linux-系统"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Linux-系统<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Rests-docs/Vuepress" class="link" aria-label="Apai-其他"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Apai-其他<!--]--></span></span><!--[--><!--]--></a></li><!--]--></ul></div></div><div class="navbar-links__item"><div class="dropdown-link"><button class="dropdown-link__title" type="button" aria-label="花圃里"><span class="xicon-container left title"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->花圃里<!--]--></span></span><span class="arrow down"></span></button><button class="dropdown-link--mobile__title" type="button" aria-label="花圃里"><span class="title"><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->花圃里<!--]--></span></span></span><span class="right arrow"></span></button><ul style="display:none;" class="dropdown-link__container"><!--[--><li class="dropdown-link__item"><a href="/docs/Apai-MyRecord/My-2023/My2307" class="link" aria-label="2023-花开万里"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->2023-花开万里<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Apai-MyRecord/My-2024/My2401" class="link" aria-label="2024-随心而来"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->2024-随心而来<!--]--></span></span><!--[--><!--]--></a></li><!--]--></ul></div></div><div class="navbar-links__item"><a href="/docs/Apai-AboutMe/Lu-aboutMe" class="link" aria-label="关于我"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->关于我<!--]--></span></span><!--[--><!--]--></a></div><!--]--></nav><span class="xicon-container btn-toggle-dark-mode btn--dark-mode"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 32 32" style="width:20px;height:20px;font-size:20px;color:inherit;"><path d="M15 2h2v3h-2z" fill="currentColor"></path><path d="M27 15h3v2h-3z" fill="currentColor"></path><path d="M15 27h2v3h-2z" fill="currentColor"></path><path d="M2 15h3v2H2z" fill="currentColor"></path><path d="M5.45 6.884l1.414-1.415l2.121 2.122l-1.414 1.414z" fill="currentColor"></path><path d="M23 7.58l2.121-2.12l1.414 1.414l-2.121 2.121z" fill="currentColor"></path><path d="M23.002 24.416l1.415-1.414l2.12 2.122l-1.413 1.414z" fill="currentColor"></path><path d="M5.47 25.13L7.59 23L9 24.42l-2.12 2.12l-1.41-1.41z" fill="currentColor"></path><path d="M16 8a8 8 0 1 0 8 8a8 8 0 0 0-8-8zm0 14a6 6 0 0 1 0-12z" fill="currentColor"></path></svg></span><span class="xicon-container btn-toggle-menus"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 32 32" style="width:20px;height:20px;font-size:20px;color:inherit;"><circle cx="16" cy="8" r="2" fill="currentColor"></circle><circle cx="16" cy="16" r="2" fill="currentColor"></circle><circle cx="16" cy="24" r="2" fill="currentColor"></circle></svg></span></div><!--]--></header><div class="mobile-menus-container"><nav class="navbar-links mobile"><!--[--><div class="navbar-links__item"><a href="/" class="link router-link-active" aria-label="Home"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Home<!--]--></span></span><!--[--><!--]--></a></div><div class="navbar-links__item"><a href="/categories/Java/1/" class="link" aria-label="分类组"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->分类组<!--]--></span></span><!--[--><!--]--></a></div><div class="navbar-links__item"><a href="/tags/Rests/1/" class="link" aria-label="标签组"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->标签组<!--]--></span></span><!--[--><!--]--></a></div><div class="navbar-links__item"><div class="dropdown-link"><button class="dropdown-link__title" type="button" aria-label="文档组"><span class="xicon-container left title"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->文档组<!--]--></span></span><span class="arrow down"></span></button><button class="dropdown-link--mobile__title" type="button" aria-label="文档组"><span class="title"><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->文档组<!--]--></span></span></span><span class="right arrow"></span></button><ul style="display:none;" class="dropdown-link__container"><!--[--><li class="dropdown-link__item"><a href="/docs/Web-develop/Html-Js" class="link" aria-label="Web-开发"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Web-开发<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Java-develop/JavaMyUtil" class="link" aria-label="Java-开发"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Java-开发<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Linux-develop/Linux" class="link" aria-label="Linux-系统"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Linux-系统<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Rests-docs/Vuepress" class="link" aria-label="Apai-其他"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Apai-其他<!--]--></span></span><!--[--><!--]--></a></li><!--]--></ul></div></div><div class="navbar-links__item"><div class="dropdown-link"><button class="dropdown-link__title" type="button" aria-label="花圃里"><span class="xicon-container left title"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->花圃里<!--]--></span></span><span class="arrow down"></span></button><button class="dropdown-link--mobile__title" type="button" aria-label="花圃里"><span class="title"><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->花圃里<!--]--></span></span></span><span class="right arrow"></span></button><ul style="display:none;" class="dropdown-link__container"><!--[--><li class="dropdown-link__item"><a href="/docs/Apai-MyRecord/My-2023/My2307" class="link" aria-label="2023-花开万里"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->2023-花开万里<!--]--></span></span><!--[--><!--]--></a></li><li class="dropdown-link__item"><a href="/docs/Apai-MyRecord/My-2024/My2401" class="link" aria-label="2024-随心而来"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->2024-随心而来<!--]--></span></span><!--[--><!--]--></a></li><!--]--></ul></div></div><div class="navbar-links__item"><a href="/docs/Apai-AboutMe/Lu-aboutMe" class="link" aria-label="关于我"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->关于我<!--]--></span></span><!--[--><!--]--></a></div><!--]--></nav><div class="appearance"><span>Appearance</span><span class="xicon-container btn-toggle-dark-mode"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 32 32" style="width:20px;height:20px;font-size:20px;color:inherit;"><path d="M15 2h2v3h-2z" fill="currentColor"></path><path d="M27 15h3v2h-3z" fill="currentColor"></path><path d="M15 27h2v3h-2z" fill="currentColor"></path><path d="M2 15h3v2H2z" fill="currentColor"></path><path d="M5.45 6.884l1.414-1.415l2.121 2.122l-1.414 1.414z" fill="currentColor"></path><path d="M23 7.58l2.121-2.12l1.414 1.414l-2.121 2.121z" fill="currentColor"></path><path d="M23.002 24.416l1.415-1.414l2.12 2.122l-1.413 1.414z" fill="currentColor"></path><path d="M5.47 25.13L7.59 23L9 24.42l-2.12 2.12l-1.41-1.41z" fill="currentColor"></path><path d="M16 8a8 8 0 1 0 8 8a8 8 0 0 0-8-8zm0 14a6 6 0 0 1 0-12z" fill="currentColor"></path></svg></span></div></div><div class="series-mask"></div><aside class="series-container"><div class="site-brand"><img class="logo" src="/logo.png" alt="阿派 | Apai Blog"><a href="/" class="site-name can-hide">阿派 | Apai Blog</a></div><!--[--><!--[--><section class="series-group series-item"><h5 class="series-heading">Java 笔记</h5><ul><li><!--[--><a href="/docs/Java-develop/JavaMyUtil.html" class="link series-item" aria-label="阿派的工具类"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->阿派的工具类<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Java-develop/Java-basics.html" class="link series-item" aria-label="Java 基础"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Java 基础<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Java-develop/JavaJinJie02.html" class="link series-item" aria-label="Java 基础_进阶二"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Java 基础_进阶二<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Java-develop/JavaGongNeng03.html" class="link series-item" aria-label="Java 功能实现"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Java 功能实现<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Java-develop/JavaBiKeng04.html" class="link series-item" aria-label="Java 避坑及技巧"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Java 避坑及技巧<!--]--></span></span><!--[--><!--]--></a><!--]--></li></ul></section><!--]--><!--[--><section class="series-group series-item"><h5 class="series-heading">Java 基础</h5><ul><li><!--[--><a href="/docs/Java-develop/JavaAPI.html" class="link series-item" aria-label="Java API"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Java API<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Java-develop/WebSocket.html" class="link series-item" aria-label="WebSocket"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->WebSocket<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Java-develop/ShiWuAffair.html" class="link series-item" aria-label="Spring Boot 事务"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Spring Boot 事务<!--]--></span></span><!--[--><!--]--></a><!--]--></li></ul></section><!--]--><!--[--><section class="series-group series-item"><h5 class="series-heading active">MySql 数据库</h5><ul><li><!--[--><a aria-current="page" href="/docs/Java-develop/MySql.html" class="router-link-active router-link-exact-active link router-link-active series-item active" aria-label="MySql 数据库"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->MySql 数据库<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Java-develop/MySqlFunction.html" class="link series-item" aria-label="MySql 函数"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->MySql 函数<!--]--></span></span><!--[--><!--]--></a><!--]--></li></ul></section><!--]--><!--[--><section class="series-group series-item"><h5 class="series-heading">Java 框架</h5><ul><li><!--[--><a href="/docs/Java-develop/Spring.html" class="link series-item" aria-label="Spring_框架"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Spring_框架<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Java-develop/SpringPeiZhi.html" class="link series-item" aria-label="Spring 配置详解"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Spring 配置详解<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Java-develop/MyBatisPlus.html" class="link series-item" aria-label="MyBatisPlus_框架"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->MyBatisPlus_框架<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Java-develop/SpringSecurity.html" class="link series-item" aria-label="SpringSecurity 安全框架"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->SpringSecurity 安全框架<!--]--></span></span><!--[--><!--]--></a><!--]--></li></ul></section><!--]--><!--[--><section class="series-group series-item"><h5 class="series-heading">微服务</h5><ul><li><!--[--><a href="/docs/Java-develop/SpringConfig.html" class="link series-item" aria-label="SpringConfig 微服务"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->SpringConfig 微服务<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Java-develop/Redis.html" class="link series-item" aria-label="Redis 内存数据库"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Redis 内存数据库<!--]--></span></span><!--[--><!--]--></a><!--]--></li><li><!--[--><a href="/docs/Java-develop/RabbitMQ.html" class="link series-item" aria-label="RabbitMQ 消息队列"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->RabbitMQ 消息队列<!--]--></span></span><!--[--><!--]--></a><!--]--></li></ul></section><!--]--><!--]--></aside><!--[--><main class="page-container"><h1 class="page-title">MySql 数据库</h1><div class="page-info"><span class="xicon-container left"><!--[--><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 32 32" class="xicon-icon" style="width:18px;height:18px;font-size:18px;color:inherit;"><path d="M16 4a5 5 0 1 1-5 5a5 5 0 0 1 5-5m0-2a7 7 0 1 0 7 7a7 7 0 0 0-7-7z" fill="currentColor"></path><path d="M26 30h-2v-5a5 5 0 0 0-5-5h-6a5 5 0 0 0-5 5v5H6v-5a7 7 0 0 1 7-7h6a7 7 0 0 1 7 7z" fill="currentColor"></path></svg><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->LuisApai<!--]--></span></span><span class="xicon-container left"><!--[--><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 32 32" class="xicon-icon" style="width:18px;height:18px;font-size:18px;color:inherit;"><path d="M26 4h-4V2h-2v2h-8V2h-2v2H6c-1.1 0-2 .9-2 2v20c0 1.1.9 2 2 2h20c1.1 0 2-.9 2-2V6c0-1.1-.9-2-2-2zm0 22H6V12h20v14zm0-16H6V6h4v2h2V6h8v2h2V6h4v4z" fill="currentColor"></path></svg><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->2023/04/26<!--]--></span></span><!----><!----><!----></div><div class="theme-reco-default-content"><div><h2 id="mysql-数据库" tabindex="-1"><a class="header-anchor" href="#mysql-数据库" aria-hidden="true">#</a> MySql_数据库</h2><ul><li>DDL(数据定义语言):数据库结构和表结构的增删改查</li><li>DML(数据操作语言):表数据的增删改</li><li>DQL(数据查询语言):表数据的查询</li><li>DCL(数据控制语言):数据库的权限控制</li></ul><h3 id="数据库防坑" tabindex="-1"><a class="header-anchor" href="#数据库防坑" aria-hidden="true">#</a> 数据库防坑</h3><blockquote><p>数据库版本 分组报错</p></blockquote><p>报错信息: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggre</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 删除 sql_mode = ONLY_FULL_GROUP_BY 这个设置</span>
<span class="token keyword">select</span> @<span class="token variable">@global.sql_mode</span><span class="token punctuation">;</span>
<span class="token keyword">select</span> version<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
@<span class="token variable">@sql_mode</span><span class="token punctuation">;</span><span class="token keyword">SET</span> sql_mode<span class="token operator">=</span><span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token keyword">REPLACE</span><span class="token punctuation">(</span>@<span class="token variable">@sql_mode</span><span class="token punctuation">,</span><span class="token string">&#39;ONLY_FULL_GROUP_BY&#39;</span><span class="token punctuation">,</span><span class="token string">&#39;&#39;</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">## 修改全局</span>
<span class="token keyword">set</span> @<span class="token variable">@global.sql_mode</span> <span class="token operator">=</span> <span class="token string">&#39;&#39;</span><span class="token punctuation">;</span>
<span class="token keyword">set</span> @<span class="token variable">@global.sql_mode</span> <span class="token operator">=</span> <span class="token string">&#39;STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION&#39;</span><span class="token punctuation">;</span>
<span class="token comment">## 修改当前</span>
<span class="token keyword">set</span> @<span class="token variable">@sql_mode</span> <span class="token operator">=</span> <span class="token string">&#39;ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION&#39;</span><span class="token punctuation">;</span>

<span class="token comment">-- 方法二: 在mysql配置文件 my.ini 添加下面配置  最后再服务重启mysql即可</span>
sql_mode <span class="token operator">=</span> STRICT_TRANS_TABLES<span class="token punctuation">,</span>ERROR_FOR_DIVISION_BY_ZERO<span class="token punctuation">,</span>NO_ENGINE_SUBSTITUTION
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><div class="language-ini line-numbers-mode" data-ext="ini"><pre class="language-ini"><code><span class="token section"><span class="token punctuation">[</span><span class="token section-name selector">mysqld</span><span class="token punctuation">]</span></span>
<span class="token comment">### 设置server_id，同一局域网中需要唯一</span>
<span class="token key attr-name">server_id</span><span class="token punctuation">=</span><span class="token value attr-value">103</span>
<span class="token key attr-name">port</span><span class="token punctuation">=</span><span class="token value attr-value">3306</span>
<span class="token key attr-name">basedir</span><span class="token punctuation">=</span><span class="token value attr-value">C:\JavaFiles\mysql-8.0.19-winx64 ## 这里替换成你自己的解压目录即可</span>
<span class="token key attr-name">datadir</span><span class="token punctuation">=</span><span class="token value attr-value">C:\JavaFiles\mysql-8.0.19-winx64\data   ## 存储数据的文件</span>
<span class="token key attr-name">max_connections</span><span class="token punctuation">=</span><span class="token value attr-value">200</span>
<span class="token key attr-name">max_connect_errors</span><span class="token punctuation">=</span><span class="token value attr-value">10</span>
<span class="token key attr-name">character-set-server</span><span class="token punctuation">=</span><span class="token value attr-value">UTF8MB4</span>
<span class="token key attr-name">default-storage-engine</span><span class="token punctuation">=</span><span class="token value attr-value">INNODB</span>
<span class="token key attr-name">default_authentication_plugin</span><span class="token punctuation">=</span><span class="token value attr-value">mysql_native_password</span>
<span class="token key attr-name">sql_mode</span> <span class="token punctuation">=</span> <span class="token value attr-value">STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION</span>

<span class="token section"><span class="token punctuation">[</span><span class="token section-name selector">mysql</span><span class="token punctuation">]</span></span>
<span class="token key attr-name">default-character-set</span><span class="token punctuation">=</span><span class="token value attr-value">UTF8MB4</span>
<span class="token key attr-name">default-character-set</span><span class="token punctuation">=</span><span class="token value attr-value">utf8</span>

<span class="token section"><span class="token punctuation">[</span><span class="token section-name selector">client</span><span class="token punctuation">]</span></span>
<span class="token key attr-name">port</span><span class="token punctuation">=</span><span class="token value attr-value">3306</span>
<span class="token key attr-name">default-character-set</span><span class="token punctuation">=</span><span class="token value attr-value">utf8</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="数据库-索引" tabindex="-1"><a class="header-anchor" href="#数据库-索引" aria-hidden="true">#</a> 数据库_索引</h2><h3 id="索引简介" tabindex="-1"><a class="header-anchor" href="#索引简介" aria-hidden="true">#</a> 索引简介</h3><p>MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。索引的本质：索引是数据结构。</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>注：在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。你可以简单理解为“排好序的快速查找数据结构。
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h3 id="存储位置和文件结构" tabindex="-1"><a class="header-anchor" href="#存储位置和文件结构" aria-hidden="true">#</a> 存储位置和文件结构</h3><p>一般来说索引本身也很大，<strong>不可能存储在内存中，因此索引往往以文件形式存储在硬盘上</strong></p><p>索引文件结构：hash、二叉树、B树、B+树</p><p>其中<strong>聚簇索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引文件结构</strong>， memory的存储引擎使用的是hash结构</p><h3 id="索引优势和劣势" tabindex="-1"><a class="header-anchor" href="#索引优势和劣势" aria-hidden="true">#</a> 索引优势和劣势</h3><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>优势：
1）类似大学图书馆建书目索引，提高数据检索效率，降低数据库的IO成本
2）通过索引列对数据进行排序，降低数据排序成本，降低了CPU的消耗

劣势：
1）虽然索引大大提高了查询速度，同时却会降低更新表的速度,如果对表INSERT,UPDATE和DELETE。因为更新表时，MySQL不仅要修改存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
2）索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立优秀的索引，或优化查询语句
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="mysql的索引文件" tabindex="-1"><a class="header-anchor" href="#mysql的索引文件" aria-hidden="true">#</a> mysql的索引文件</h3><blockquote><p>在msql5.6版本中，如果是MyIsam引擎：那么一个表有三个文件，</p></blockquote><p>如：C:\dev\mysql\data\mysql\下的columns_priv表，就有columns_priv.frm（表结构文件），columns_priv.MYD（表数据文件），columns_priv.MYI（表索引文件）</p><p>在msql5.6版本中，如果是InnoDB引擎：对于每一个表，都有一个xxx.frm的表结构文件和xxx.ibd的文件，这个xxx.ibd文件就是存放表的数据和表的索引的文件，也就是表的索引文件和数据文件保存到了一个文件中，注意：xxx.ibd文件默认是隐藏的，如：C:\ProgramData\MySQL\MySQL Server 5.5\data\hospital目录里面我们只能看到hospital库的表的结构文件，看不到ibd文件。</p><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>show variables like &#39;%per_table%&#39;   #可以发现默认是off
set @@global.innodb_file_per_table=on;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><p>另外在mysql8的版本中，show variables like &#39;%per_table%&#39;默认是on，另外在myql8中，并不单独提供xxx.frm表结构文件，而是合并在xxx.ibd文件中，Oracle官方将frm文件的信息以及更多信息移动到叫做序列化字典（SDI），SDI倍写在ibd文件内部</p><h2 id="索引-结构-语法-分类" tabindex="-1"><a class="header-anchor" href="#索引-结构-语法-分类" aria-hidden="true">#</a> 索引 结构 语法 分类</h2><h3 id="索引结构" tabindex="-1"><a class="header-anchor" href="#索引结构" aria-hidden="true">#</a> 索引结构</h3><h4 id="btree树结构" tabindex="-1"><a class="header-anchor" href="#btree树结构" aria-hidden="true">#</a> BTREE树结构</h4><h4 id="hash结构" tabindex="-1"><a class="header-anchor" href="#hash结构" aria-hidden="true">#</a> hash结构</h4><blockquote><p>是指使用某种哈希函数实现key-&gt;value 映射的索引结构</p></blockquote><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>Hash索引只有 Memory, NDB两种引擎支持，Memory引擎默认支持Hash索引，如果多个hash值相同，出现哈希碰撞，那么索引以链表方式存储。NoSql采用此中索引结构。由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

因此，哈希索引适用于等值检索，通过一次哈希计算即可定位数据的位置
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><img src="https://apaiimages.oss-cn-guangzhou.aliyuncs.com/MD/image-20210706194207799.png" alt="image-20210706194207799"></p><h4 id="full-text全文索引" tabindex="-1"><a class="header-anchor" href="#full-text全文索引" aria-hidden="true">#</a> full-text全文索引</h4><blockquote><p>全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果</p></blockquote><p><strong>全文索引用match+against方式查询：</strong></p><ul><li><p>不同于like方式的的查询：SELECT * FROM article WHERE content LIKE ‘%查询字符串%’;</p></li><li><p>SELECT * FROM article WHERE MATCH(title,content) AGAINST (‘查询字符串’); 明显的提高查询效率。它的出现是为了解决WHERE name LIKE “%word%&quot;这类针对文本的模糊查询效率较低的问题。</p></li></ul><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>注意：mysql5.6.4以前只有Myisam支持，5.6.4版本以后innodb才支持，但是官方版本不支持中文分词，需要第三方分词插件。5.7以后官方支持中文分词。随着大数据时代的到来，关系型数据库应对全文索引的需求已力不从心，逐渐被 solr,elasticSearch等专门的搜索引擎所替代。在全文检索中，又分正排索引和倒排索引，也就是说正排索引和倒排索引，针对是全文的检索，MySQL既不是倒排索引，也不是正排索引
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h4 id="r-tree索引-了解" tabindex="-1"><a class="header-anchor" href="#r-tree索引-了解" aria-hidden="true">#</a> R-Tree索引(了解)</h4><p>R-Tree在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有myisam、bdb、innodb、ndb、archive几种。相对于b-tree，<strong>r-tree的优势在于范围查找</strong></p><h4 id="聚簇与非聚簇索引区别" tabindex="-1"><a class="header-anchor" href="#聚簇与非聚簇索引区别" aria-hidden="true">#</a> 聚簇与非聚簇索引区别</h4><p>mysql的索引类型跟存储引擎是相关的，innodb存储引擎数据文件跟索引文件全部放在ibd文件中，而myisam的数据文件放在扩展名为myd文件中，索引放在扩展名为myI文件中，</p><p><strong>其实区分聚族索引和非聚族索引非常简单，只要判断数据跟索引是否存储在一起就可以了。</strong></p><p>innodb存储引擎在进行数据插入的时候，数据必须要限索引放在一起,如果有主键就使用主键，没有主键就使用唯一键，没有唯一键就使用6字节的rowid,因此跟数据绑定在一起的就是聚簇索引， 而为了避免数据冗余存储，其他的索引的叶子节点中存储的都是聚族索引的key值，因此innodb中既有聚簇索引也有非聚簇索引，而myisam中只有非聚簇索引。</p><p>注意事项：</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>1）、在innodb引擎中，一个表最多只能有一个聚簇索引，一个表也必须有一个聚簇索引
2）、只有innodb数据引擎支持聚簇索引，myisam不支持，在innodb中，采用主键作为聚簇索引
3）、如果当前表中没有主键，mysql将会选择一个唯一性的字段作为聚簇索引
4）、如果当前表既没有主键字段，也没有添加唯一约束字段，mysql将随机选取一个字段作为聚簇索引
5）、在表中其它字段上创建的索引都是非聚簇索引
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="索引的语法" tabindex="-1"><a class="header-anchor" href="#索引的语法" aria-hidden="true">#</a> 索引的语法</h3><h4 id="创建索引" tabindex="-1"><a class="header-anchor" href="#创建索引" aria-hidden="true">#</a> 创建索引</h4><p>1）CREATE [UNIQUE] INDEX 索引名 ON 表名(列名);</p><p>2）ALTER TABLE 表名 ADD [UNIQUE] INDEX [索引名] ON(列名);</p><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>-- ALTER有四种方式来添加数据表的索引：
-- 1.该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE 表名 ADD PRIMARY KEY (列名)
-- 2.这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE 表名 ADD UNIQUE 索引名 (列名)
-- 3.添加普通索引，索引值可出现多次。
ALTER TABLE 表名 ADD INDEX 索引名 (列名)
-- 4.该语句指定了索引为 FULLTEXT ，用于全文索引。
ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名)
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="查看索引" tabindex="-1"><a class="header-anchor" href="#查看索引" aria-hidden="true">#</a> 查看索引</h4><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>show index from 表名
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h4 id="删除索引" tabindex="-1"><a class="header-anchor" href="#删除索引" aria-hidden="true">#</a> 删除索引</h4><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>DROP INDEX 索引名 ON 表名;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h3 id="索引分类" tabindex="-1"><a class="header-anchor" href="#索引分类" aria-hidden="true">#</a> 索引分类</h3><h4 id="单值索引" tabindex="-1"><a class="header-anchor" href="#单值索引" aria-hidden="true">#</a> 单值索引</h4><blockquote><p>即一个索引只包含单个列，一个表可以有多个单列索引</p></blockquote><p>说明：索引建立成哪种索引类型？根据数据引擎类型自动选择的索引类型。innodb引擎都采用B+Tree索引，而且innodb 引擎的主键索引默认为聚簇索引，myisam <strong>则都采用的</strong> <strong>B-TREE</strong>索引</p><p><strong>创建索引，并指定主键索引</strong></p><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>mysql&gt; CREATE TABLE customer (id INT(10)  PRIMARY KEY AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200)
);
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><p><strong>创建单值索引</strong></p><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>mysql&gt; create index 索引名 ON 表名(列名)
mysql&gt; create index idx_customer_name ON customer(customer_name)
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><p><strong>查询索引</strong></p><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>mysql&gt; show index from 表名;   #这个表其实就有2个索引
mysql&gt; show index from customer;   #这个表其实就有2个索引
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><p><strong>删除索引</strong></p><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>DROP INDEX 索引名 on 表名 ;
DROP INDEX idx_customer_name on customer ;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="唯一索引" tabindex="-1"><a class="header-anchor" href="#唯一索引" aria-hidden="true">#</a> 唯一索引</h4><blockquote><p>如果当前表中字段添加了唯一性约束，mysql主动的将当前字段上的数据进行排序，其生成的索引就是唯一索引，索引列的值必须唯一，但允许有空值</p></blockquote><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>CREATE TABLE customer (id int primary key not null auto_increment,
customer_no varchar(16),customer_name VARCHAR(200),UNIQUE key(customer_no)
);

## 单独建唯一索引：
create unique index idx_customer_no ON customer(customer_no); 
create unique index 索引名称 ON 表名(列名); 
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>注：建立唯一索引时必须保证所有的值是唯一的（除了null），若有重复数据，会报错。</p><h4 id="复合-组合索引" tabindex="-1"><a class="header-anchor" href="#复合-组合索引" aria-hidden="true">#</a> 复合| 组合索引</h4><blockquote><p>在数据库操作期间，用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引)，即一个索引包含多个列，复合索引比单值索引所需要的开销更小(对于相同的多个列建索引)</p></blockquote><p><strong>防坑指南</strong></p><ul><li>查询的条件顺序建议跟索引的顺序一致 | 不一致时会自动优化顺序 (最左原则)</li><li>最左原则: 必须保证 条件和索引的第一个值 在条件里必须存在</li><li>如果查询的条件 有范围 则后面的索引必然的失效 导致排序</li></ul><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>create table customer(id int(10) UNSIGNED primary key auto_increment,customer_no varchar(200),customer_name varchar(200),unique key(customer_name),key(customer_no,customer_name))
 
## 单独建索引：
create index 索引名 on 表名(列名1, 列名2, 列名3)
create index idx_no_name on customer(customer_no,customer_name);
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="主键索引" tabindex="-1"><a class="header-anchor" href="#主键索引" aria-hidden="true">#</a> 主键索引</h4><blockquote><p>如果当前表中字段添加了主键约束，mysql主动的将当前字段上数据进行排序，其生成的索引就是主键索引，<strong>设定为主键后数据库会自动建立索引</strong>，在innodb引擎中，这种索引也是聚簇索引结构</p></blockquote><p><strong>注：使用 AUTO_INCREMENT 关键字的列必须有索引(只要有索引就行)。</strong></p><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>create table customer(id int(10) UNSIGNED auto_increment,customer_no varchar(200),customer_name varchar(200),primary key(id))
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h3 id="如何合理创建索引" tabindex="-1"><a class="header-anchor" href="#如何合理创建索引" aria-hidden="true">#</a> 如何合理创建索引</h3><h4 id="建议创建索引" tabindex="-1"><a class="header-anchor" href="#建议创建索引" aria-hidden="true">#</a> 建议创建索引</h4><p>1.主键自动建立唯一索引</p><p>2.频繁作为查询的条件的字段应该创建索引</p><p>3.查询中与其他表关联的字段，外键关系建立索引</p><p>4.频繁更新的字段不适合创建索引（因为每次更新不单单是更新了记录还会更新索引，加重IO负担）</p><p>5.查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度</p><p>6.查询中统计或者分组字段</p><h4 id="不建议创建索引" tabindex="-1"><a class="header-anchor" href="#不建议创建索引" aria-hidden="true">#</a> 不建议创建索引</h4><p>1.表记录太少</p><p>2.经常增删改的表</p><p>3.数据重复且分布平均的表字段，因此应该只为经常查询和经常排序的数据列建立索引。</p><p>注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果</p><h2 id="mysql-性能分析及调优" tabindex="-1"><a class="header-anchor" href="#mysql-性能分析及调优" aria-hidden="true">#</a> Mysql 性能分析及调优</h2><h3 id="explain-性能分析" tabindex="-1"><a class="header-anchor" href="#explain-性能分析" aria-hidden="true">#</a> Explain 性能分析</h3><blockquote><p>在Mysql中，有一个专门负责优化SELECT语句的优化器模块（Mysql Query optimizer（优化）)</p></blockquote><h4 id="主要功能" tabindex="-1"><a class="header-anchor" href="#主要功能" aria-hidden="true">#</a> 主要功能：</h4><ul><li>通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划 ，当客户端向MySQL请求一条Query语句到命令解析器模块完成请求分类区别出是SELECT并转发给QueryOptimizer之后，QueryOptimizer首先会对整条Query进行优化处理掉一些常量表达式的预算，直接换算成常量值。</li><li>并对Query中的查询条件进行简化和转换，如去掉一些无用或者显而易见的条件，结构调整等等。然后则是分析Query中的Hint信息（如果有），看显示Hint信息是否可以完全确定该Query的执行计划。</li><li>如果没有Hint或者Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划</li></ul><h3 id="explain-详解" tabindex="-1"><a class="header-anchor" href="#explain-详解" aria-hidden="true">#</a> Explain 详解</h3><p>使用EXPLAIN关键字可以模拟优化器执行SQL语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是结构的性能瓶颈</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">explain</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> mysql<span class="token punctuation">.</span><span class="token keyword">user</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p><img src="https://apaiimages.oss-cn-guangzhou.aliyuncs.com/MD/image-20220819114220876.png" alt="image-20220819114220876"></p><h4 id="id-列" tabindex="-1"><a class="header-anchor" href="#id-列" aria-hidden="true">#</a> ID 列</h4><blockquote><p>id：包含一组数字，表示查询中执行select子句或操作表的顺序，</p></blockquote><ul><li><p>如果id相同，执行顺序由上至下，可以认为是一组，从上往下顺序执行，</p></li><li><p>在所有组中，id值越大，优先级越高，越先执行；</p></li><li><p>如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行</p></li></ul><h4 id="select-type-列" tabindex="-1"><a class="header-anchor" href="#select-type-列" aria-hidden="true">#</a> select_type 列</h4><blockquote><p>表示查询中每个select子句的类型：SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT</p></blockquote><ul><li>SIMPLE：查询中不包含子查询或者UNION</li><li>PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为</li><li>SUBQUERY：在SELECT或WHERE列表中包含了子查询，该子查询被标记为SUBQUERY</li><li>DERIVED：在FROM列表中包含的子查询被标记为：DERIVED（衍生）</li><li>UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED</li><li>从UNION表获取结果的SELECT被标记为：UNION RESULT</li></ul><h4 id="table-列" tabindex="-1"><a class="header-anchor" href="#table-列" aria-hidden="true">#</a> table 列</h4><blockquote><p>显示这一行数据时关于哪个表的</p></blockquote><h4 id="type-列" tabindex="-1"><a class="header-anchor" href="#type-列" aria-hidden="true">#</a> type 列</h4><ul><li><p>表示MySQL在表中找到所需行的方式，又称“访问类型”</p></li><li><p>type 扫描方式由快到慢：<strong>system &gt; const &gt; eq_ref &gt; ref &gt; range &gt; index &gt; ALL</strong></p></li><li><p>总结：<strong>一般来说，得保证查询只是达到range级别，最好达到ref</strong></p></li></ul><blockquote><h6 id="system" tabindex="-1"><a class="header-anchor" href="#system" aria-hidden="true">#</a> system</h6></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 它是const联接类型的特例，表只有一行记录（等于系统表），很少出现</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><blockquote><h6 id="const" tabindex="-1"><a class="header-anchor" href="#const" aria-hidden="true">#</a> const</h6></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 通过索引一次就能找到，速度非常快。const用于比较【primary key或者unique索引】</span>
<span class="token comment">-- 被连接的部分是一个常量值（const），只匹配一行数据，所以很快。通常将主键至于where列表中</span>

<span class="token keyword">explain</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> employee <span class="token keyword">where</span> id <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><h6 id="eq-ref" tabindex="-1"><a class="header-anchor" href="#eq-ref" aria-hidden="true">#</a> eq_ref</h6></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 对于前表中的每一行（row），对应后表只有一行被扫描，这类扫描的速度也非常的快。</span>
<span class="token comment">-- 应用场景：1.联表（join）查询；2.命中主键或者非空唯一索引；3.等值连接</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><h6 id="ref" tabindex="-1"><a class="header-anchor" href="#ref" aria-hidden="true">#</a> ref</h6></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 非唯一性索引扫描，返回匹配某个单独值的所有行，场景：联表查询普通非唯一索引 </span>
<span class="token comment">-- 由于后表使用了`普通非唯一索引`，对于前表`user1`表的每一行(row)，后表`user_balance`表可能有多于一行的数据被扫描</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><h6 id="range" tabindex="-1"><a class="header-anchor" href="#range" aria-hidden="true">#</a> range</h6></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，</span>
<span class="token comment">-- 一般就是在你的where语句中出现了between、&lt;、&gt;、in等的查询，</span>
<span class="token comment">-- 这种范围扫描索引扫描比全表扫描要好，因为他只需要开始索引的某一点，而结束语另一点，不用扫描全部索引</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><h6 id="index" tabindex="-1"><a class="header-anchor" href="#index" aria-hidden="true">#</a> index</h6></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- index与ALL区别为index类型只遍历索引树。</span>
<span class="token comment">-- 这通常比ALL快，只快一点，因为索引文件通常比数据文件小。</span>
<span class="token comment">--（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><h6 id="all" tabindex="-1"><a class="header-anchor" href="#all" aria-hidden="true">#</a> All</h6></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- all：Full Table Scan， MySQL将遍历全表以找到匹配的行</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h4 id="possible-keys-列" tabindex="-1"><a class="header-anchor" href="#possible-keys-列" aria-hidden="true">#</a> possible_keys 列</h4><p>显示可能应用在这张表中的索引一个或多个。查询涉及的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用，这是理论分析</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">explain</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> employee <span class="token keyword">where</span> name<span class="token operator">=</span><span class="token string">&#39;1&#39;</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p><img src="https://apaiimages.oss-cn-guangzhou.aliyuncs.com/MD/image-20210706213624564.png" alt="image-20210706213624564"></p><h4 id="key-列" tabindex="-1"><a class="header-anchor" href="#key-列" aria-hidden="true">#</a> key 列</h4><p>实际使用的索引，如果为null则没有使用索引，如果不为null，则表示使用了索引，有可能使用了覆盖索引</p><p><strong>覆盖索引：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快</strong></p><blockquote><p>覆盖索引</p></blockquote><p><strong>使用覆盖索引的常见的方法是：将被查询的字段，建立到联合索引里去</strong></p><ul><li>id 字段是聚簇索引，age 字段是普通索引（二级索引）</li><li>id 的树的叶子节点包含了行数据 而age的数节点并没有行数据只有对应的id</li></ul><div class="language-java line-numbers-mode" data-ext="java"><pre class="language-java"><code><span class="token comment">// 索引的存储结构：id 是主键，所以是聚簇索引，其叶子节点存储的是对应行记录的数据</span>
<span class="token comment">// 如果查询条件为主键（聚簇索引），则只需扫描一次B+树即可通过聚簇索引定位到要查找的行记录数据。</span>
<span class="token comment">// age 是普通索引（二级索引），非聚簇索引，其叶子节点存储的是聚簇索引的的值</span>
    
如果查询条件为普通索引（非聚簇索引），需要扫描两次<span class="token class-name">B</span><span class="token operator">+</span>树，第一次扫描通过普通索引定位到聚簇索引的值，然后第二次扫描通过聚簇索引的值定位到要查找的行记录数据，这种也叫回表操作，所谓回表操作：先通过普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引<span class="token class-name">B</span><span class="token operator">+</span>树，它的性能较扫一遍索引树更低
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="key-len-列" tabindex="-1"><a class="header-anchor" href="#key-len-列" aria-hidden="true">#</a> key_len 列</h4><blockquote><p>表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。</p></blockquote><p>在不损失精确性的情况下，长度越短越好,key_len显示的值为索引最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的，如果实际上用到索引，则有索引的长度，否则为null</p><h4 id="ref-列" tabindex="-1"><a class="header-anchor" href="#ref-列" aria-hidden="true">#</a> ref 列</h4><blockquote><p>显示使用哪个列或常数与key一起从表中选择行</p></blockquote><h4 id="rows-列" tabindex="-1"><a class="header-anchor" href="#rows-列" aria-hidden="true">#</a> rows 列</h4><blockquote><p>显示MySQL认为它执行查询时必须检查的行数，</p></blockquote><p>这个数字是内嵌循环关联计划里的循环数目，也就是说它不是MySql认为它最终要从表里读取出来的行数，而是MySql为了找到符合查询的每一点上标准的那些行而必须读取的行的平均数</p><h4 id="extra-列" tabindex="-1"><a class="header-anchor" href="#extra-列" aria-hidden="true">#</a> extra 列</h4><p><strong>这一列包含的是不适合在其他列显示的额外信息，其值有：</strong></p><p>1.Using filesort：mysql会对数据使用一个外部的索引排序，而不是<strong>按照表内的索引顺序进行</strong>读取，MySQL中无法用索引完成排序操作成为“文件排序&quot;，极大影响mysql性能，需要尽快优化</p><blockquote><p>排序的时候最好遵循所建索引的顺序与个数否则就可能会出现usering filesort</p></blockquote><p>2.Using temporary： 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表，极大影响mysql性能，需要尽快优化，常见于排序order by 和分组查询 group by</p><blockquote><p>结论：group by一定要遵循所建索引的顺序与个数</p></blockquote><p>3.Using index：表示相应的select操作中使用了覆盖索引(covering index)，避免回表操作，效率good</p><p>4.Using where：表名使用了where过滤，效率一般</p><blockquote><p>其中using filesort，using temporary，using index最为常见，出现前两种表示是需要优化的地方，出现第三种表示索引效率不错，如果值是null，则表明要进行回表操作</p></blockquote><h3 id="mysql-调优-索引优化" tabindex="-1"><a class="header-anchor" href="#mysql-调优-索引优化" aria-hidden="true">#</a> Mysql 调优-索引优化</h3><h4 id="单表优化" tabindex="-1"><a class="header-anchor" href="#单表优化" aria-hidden="true">#</a> 单表优化</h4><blockquote><p>对于单表查询，根据 where 后面的字段建立索引，遇到有 &lt;、&gt;、!= 这样的关系运算符，会使已经建完的索引失效</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- type 变成了 range,这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。</span>

<span class="token comment">-- 但是我们已经建立了索引,为啥没用呢?这是因为按照 BTree 索引的工作原理,先排序 category_id,如果遇到相同的 category_id 则再排序 comments,如果遇到相同的 comments 则再排序 views。</span>

<span class="token comment">-- 当 comments 字段在联合索引里处于中间位置时,因comments &gt; 1 条件是一个范围值(所谓 range),MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="两表优化" tabindex="-1"><a class="header-anchor" href="#两表优化" aria-hidden="true">#</a> 两表优化</h4><blockquote><p>注意: 因为 左连接在查询时已经全表查了 还未到wheel条件那 则必须到查询右表的时候配合where条件建立索引</p></blockquote><ul><li><p>左连接，一定要要把索引创建到右表上</p></li><li><p>右连接，一定要要把索引创建到左表上</p></li></ul><h4 id="总结" tabindex="-1"><a class="header-anchor" href="#总结" aria-hidden="true">#</a> 总结</h4><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>1、对于单表查询，一定要根据 where 后面的字段建立索引，遇到有 &lt;、&gt;、!= 这样的关系运算符，会使已经建完的索引失效
2、对于双表查询，左外连接只会因为右表的索引而被优化，右外连接只会因为左表的索引而被优化，内连接则因为两个表的索引都可以被优化
3、对于三表查询，则根据是左连接对右边的连接建立索引，根据右连接对左边的连接建立索引
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="索引失效情况-重点" tabindex="-1"><a class="header-anchor" href="#索引失效情况-重点" aria-hidden="true">#</a> 索引失效情况（重点）</h3><p>1、组合索引不遵循最左匹配原则会导致索引失效</p><p>2、组合索引的前面索引列使用范围查询(&lt;&gt;,like)会导致后续的索引失效</p><p>3、不要在索引上做任何操作(计算，函数，类型转换)</p><p>4、is null和is not null无法使用索引</p><p>5、尽量少使用or操作符，否则连接时索引会失效</p><p>6、字符串不添加引号会导致索引失效</p><p>7、两表关联使用的条件字段中字段的长度、编码不一致会导致索引失效</p><p>8、like语句中，以%开头的模糊查询，会导致索引失效</p><p>9、如果mysq|中使用全表扫描比使用索引快，也会导致索引失效</p><h3 id="索引注意点" tabindex="-1"><a class="header-anchor" href="#索引注意点" aria-hidden="true">#</a> 索引注意点</h3><blockquote><h5 id="最好全值匹配" tabindex="-1"><a class="header-anchor" href="#最好全值匹配" aria-hidden="true">#</a> 最好全值匹配</h5></blockquote><p>建立几个复合索引字段，最好就用上几个字段。且按照顺序来用</p><p>如：索引idx_staffs_nameAgePos 建立索引时以 name ， age ，pos 的顺序建立的。全值匹配表示按顺序匹配的</p><blockquote><h5 id="最佳左前缀法则" tabindex="-1"><a class="header-anchor" href="#最佳左前缀法则" aria-hidden="true">#</a> 最佳左前缀法则</h5></blockquote><p>如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列（即name索引列）开始，不跳过索引中间的列（即age索引列）。</p><blockquote><h5 id="不在索引列上做任何操作" tabindex="-1"><a class="header-anchor" href="#不在索引列上做任何操作" aria-hidden="true">#</a> 不在索引列上做任何操作</h5></blockquote><p>不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），否则会导致索引失效而转向全表扫描</p><blockquote><h5 id="范围之后全失效" tabindex="-1"><a class="header-anchor" href="#范围之后全失效" aria-hidden="true">#</a> 范围之后全失效</h5></blockquote><p>存储引擎不能使用索引列作为范围条件比较，范围条件右边的列都失效。（范围之后全失效），若中间索引列用到了范围（&gt;、&lt;、like等），则后面的所以全失效</p><blockquote><h4 id="尽量使用覆盖索引" tabindex="-1"><a class="header-anchor" href="#尽量使用覆盖索引" aria-hidden="true">#</a> 尽量使用覆盖索引</h4></blockquote><p>尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，少用select * 查询 ，即不要查询所有列</p><blockquote><h5 id="不要使用不等于-或者" tabindex="-1"><a class="header-anchor" href="#不要使用不等于-或者" aria-hidden="true">#</a> 不要使用不等于(!= 或者&lt;&gt;)</h5></blockquote><p>mysql 在使用不等于(!= 或者&lt;&gt;)的时候无法使用索引会导致全表扫描</p><blockquote><h5 id="is-not-null-也无法使用索引" tabindex="-1"><a class="header-anchor" href="#is-not-null-也无法使用索引" aria-hidden="true">#</a> is not null 也无法使用索引</h5></blockquote><p>is not null和is null 也无法使用索引</p><blockquote><h5 id="like以通配符开头-abc-索引失效" tabindex="-1"><a class="header-anchor" href="#like以通配符开头-abc-索引失效" aria-hidden="true">#</a> like以通配符开头(&#39;%abc...&#39;)索引失效</h5></blockquote><p>like以通配符开头(&#39;%abc...&#39;)mysql索引失效会变成全表扫描的操作</p><div class="language-java line-numbers-mode" data-ext="java"><pre class="language-java"><code><span class="token comment">// 在实际开发中，有时就是要把%写到左右两边，如何也能用上索引，不让索引失效？</span>
<span class="token comment">// 使用覆盖索引，建的索引和查的字段个数顺序最好完全一致</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><h5 id="字符串不加单引号索引失效" tabindex="-1"><a class="header-anchor" href="#字符串不加单引号索引失效" aria-hidden="true">#</a> 字符串不加单引号索引失效</h5></blockquote><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>EXPLAIN SELECT * FROM staffs WHERE name = 2000;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>数据库会把2000隐式的自动的转换为String类型，但是在索引列上做任何操作（计算，函数，（自动or手动）类型转换），会使索引失效</p><blockquote><h5 id="少用or-用它来连接时会索引失效" tabindex="-1"><a class="header-anchor" href="#少用or-用它来连接时会索引失效" aria-hidden="true">#</a> 少用or,用它来连接时会索引失效</h5></blockquote><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>EXPLAIN SELECT * FROM staffs WHERE name  like &quot;abc%&quot; or age = 23
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h2 id="mysql-表数据操作" tabindex="-1"><a class="header-anchor" href="#mysql-表数据操作" aria-hidden="true">#</a> MySql 表数据操作</h2><h3 id="ddl-表-操作" tabindex="-1"><a class="header-anchor" href="#ddl-表-操作" aria-hidden="true">#</a> DDL: 表 操作</h3><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 创建表</span>
​	<span class="token keyword">create</span> <span class="token keyword">table</span> 表名<span class="token punctuation">(</span>列名 数据类型<span class="token punctuation">,</span>列名 数据类型<span class="token punctuation">,</span>列名<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">-- 删除表</span>
​	<span class="token keyword">drop</span> <span class="token keyword">table</span> 表名<span class="token punctuation">;</span>
<span class="token comment">-- 修改表-添加列</span>
​	<span class="token keyword">alter</span> <span class="token keyword">table</span> 表名 <span class="token keyword">add</span> 列名 数据类型<span class="token punctuation">;</span>
<span class="token comment">-- 查看数据库的所有表</span>
​	<span class="token keyword">show</span> <span class="token keyword">tables</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="表字段-约束" tabindex="-1"><a class="header-anchor" href="#表字段-约束" aria-hidden="true">#</a> 表字段 约束</h3><p>约束可以让字段列名的值进行规范 约束加在字段后</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 约束:</span>
	<span class="token comment">-- 非空约束 -- 数据不能为空</span>
		<span class="token operator">not</span> <span class="token boolean">null</span> 
	<span class="token comment">-- 唯一约束 -- 数据具有唯一性,不能重复</span>
		<span class="token keyword">unique</span>  
	<span class="token comment">-- 默认约束 -- 数据为空时使用默认值数据</span>
		<span class="token keyword">default</span> 默认值 
	<span class="token comment">-- 检查约束 -- 检查数据是否为选定值,数据可以为空值</span>
		<span class="token keyword">check</span> <span class="token punctuation">(</span>列名<span class="token operator">=</span>值 <span class="token operator">or</span> 列名<span class="token operator">=</span>值<span class="token punctuation">)</span> 
	<span class="token comment">-- 主键约束 -- 主键不能为空不能重复 表中只有一个主键,约束了主键的唯一性</span>
		<span class="token keyword">primary</span> <span class="token keyword">key</span> 
	<span class="token comment">-- 自增约束 -- 实现主键的自动增长 可以不赋值,可以指定值,但是每次自增长都以最大值自增</span>
		<span class="token keyword">auto_increment</span> 
	<span class="token comment">-- 外键约束 -- 外键只能关联到另外一个表的主键,并且只能使用主键已存在的数据 </span>
	<span class="token comment">-- 配合使用在主键上,可以赋值为null,但会自增覆盖空值</span>
		<span class="token keyword">foreign</span> <span class="token keyword">key</span><span class="token punctuation">(</span>外键名<span class="token punctuation">)</span> <span class="token keyword">references</span> 表名<span class="token punctuation">(</span>主键名<span class="token punctuation">)</span>
	<span class="token comment">-- 去重 | DISTINCT</span>
		<span class="token keyword">SELECT</span>  <span class="token keyword">DISTINCT</span>  <span class="token operator">*</span>  <span class="token keyword">FROM</span>  <span class="token keyword">TABLE</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="字段类型" tabindex="-1"><a class="header-anchor" href="#字段类型" aria-hidden="true">#</a> 字段类型</h3><h4 id="数值" tabindex="-1"><a class="header-anchor" href="#数值" aria-hidden="true">#</a> 数值</h4><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 整数:</span>
	<span class="token keyword">int</span>
	<span class="token keyword">bigint</span>
<span class="token comment">-- 浮点数:</span>
	<span class="token keyword">float</span> <span class="token comment">-- 单精度: 八位</span>
	<span class="token keyword">double</span> <span class="token comment">-- 双精度: 十六位  -- 可在后面加约束,如double(m,d)  m为最大长度,d为小数的长度</span>
	<span class="token keyword">decimal</span><span class="token punctuation">(</span>m<span class="token punctuation">,</span>d<span class="token punctuation">)</span> <span class="token comment">-- 定点数:适用于金额 参数m&lt;65总个数,d&lt;30且d&lt;m为小数位,浮点型存放的是近似值，而定点类型是精确值。</span>
	<span class="token comment">-- 注意: 在java里 关于金额的数据 可使用 BigDecimal 类型对应 赋值 new BigDecimal(&quot;1002&quot;); 推荐使用双引号</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="字符串" tabindex="-1"><a class="header-anchor" href="#字符串" aria-hidden="true">#</a> 字符串</h4><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 字符串</span>
	<span class="token keyword">char</span><span class="token punctuation">(</span>n<span class="token punctuation">)</span> <span class="token comment">-- 固定长度-255字符 n:表示不能超过此长度-当char(n)长度不足n时也按n计算长度</span>
	<span class="token keyword">varchar</span><span class="token punctuation">(</span>n<span class="token punctuation">)</span> <span class="token comment">-- 可变长度-65535字符</span>
	<span class="token keyword">tinytext</span> <span class="token comment">-- 单可变长度-225字符</span>
	<span class="token keyword">text</span> <span class="token comment">-- 可变长度-65535字符 富文本编辑器</span>
	<span class="token keyword">mediumtext</span> <span class="token comment">-- 可变长度-2的24次方</span>
	<span class="token keyword">longtext</span> <span class="token comment">-- 可变长度-2的32次方</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="日期" tabindex="-1"><a class="header-anchor" href="#日期" aria-hidden="true">#</a> 日期</h4><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 日期</span>
	<span class="token keyword">date</span> <span class="token comment">-- &#39; 年 - 月 - 日 &#39;</span>
	<span class="token keyword">time</span> <span class="token comment">-- &#39; 时 : 分 : 秒 &#39;</span>
	<span class="token keyword">datetime</span> <span class="token comment">-- &#39; 年 - 月 - 日  时 : 分 : 秒 &#39;</span>
	<span class="token comment">-- 添加该函数可使用系统当前时间 sysdate()</span>
	<span class="token keyword">timestamp</span> <span class="token comment">-- 自动储存记录修改时间 若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。</span>
	
	<span class="token comment">-- 注意: 在java 时间类型可对应 string  在映射文件进行表数据的追加 now() 表示追加当前的系统时间</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="dml-增删改" tabindex="-1"><a class="header-anchor" href="#dml-增删改" aria-hidden="true">#</a> DML: 增删改</h3><h4 id="insert-增" tabindex="-1"><a class="header-anchor" href="#insert-增" aria-hidden="true">#</a> insert 增</h4><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 向表中的所有列新增数据 列名-类型-都要对应</span>
	<span class="token keyword">insert</span> <span class="token keyword">into</span> 表名 <span class="token keyword">values</span><span class="token punctuation">(</span>值<span class="token number">1</span><span class="token punctuation">,</span>值<span class="token number">2</span><span class="token punctuation">,</span>值<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">-- 指定列插入数据 可插入多个列名,用逗号隔开,也要对应值</span>
	<span class="token keyword">insert</span> <span class="token keyword">into</span> 表名<span class="token punctuation">(</span>列名<span class="token punctuation">,</span> 列名<span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token string">&#39;值&#39;</span><span class="token punctuation">,</span> <span class="token string">&#39;值1&#39;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">-- 添加多行数据</span>
	<span class="token keyword">insert</span> <span class="token keyword">into</span> 表名 <span class="token keyword">values</span><span class="token punctuation">(</span>值<span class="token number">1</span><span class="token punctuation">,</span>值<span class="token number">2</span><span class="token punctuation">,</span>值<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span>值<span class="token number">1</span><span class="token punctuation">,</span>值<span class="token number">2</span><span class="token punctuation">,</span>值<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="delete-删" tabindex="-1"><a class="header-anchor" href="#delete-删" aria-hidden="true">#</a> delete 删</h4><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 删除指定列中的指定值行 --&gt; and - 且  or - 或</span>
	<span class="token keyword">delete</span> <span class="token keyword">from</span> 表名 <span class="token keyword">where</span> 条件<span class="token punctuation">;</span>
<span class="token comment">-- 清空表所有的数据</span>
	<span class="token keyword">delete</span> <span class="token keyword">from</span> 表名<span class="token punctuation">;</span>
<span class="token comment">-- 根据id批量删除</span>
	<span class="token keyword">delete</span> <span class="token keyword">from</span> 表名 <span class="token keyword">where</span> id <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="update-改" tabindex="-1"><a class="header-anchor" href="#update-改" aria-hidden="true">#</a> update 改</h4><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 修改指定行的列值</span>
	<span class="token keyword">update</span> 表名 <span class="token keyword">set</span> 列名<span class="token operator">=</span>新值 <span class="token keyword">where</span> 条件<span class="token punctuation">;</span>
<span class="token comment">-- 修改指定行的列值基础增加</span>
	<span class="token keyword">update</span> 表名 <span class="token keyword">set</span> 列名<span class="token operator">=</span>列名<span class="token operator">+</span>值 <span class="token keyword">where</span> 条件<span class="token punctuation">;</span>
<span class="token comment">-- 修改指定行的多个列值</span>
	<span class="token keyword">update</span> 表名 <span class="token keyword">set</span> 列名<span class="token number">1</span><span class="token operator">=</span>新值<span class="token punctuation">,</span>列名<span class="token number">2</span><span class="token operator">=</span>新值 <span class="token keyword">where</span> 条件<span class="token punctuation">;</span>
<span class="token comment">-- 根据id批量修改</span>
	<span class="token keyword">update</span> 表名 <span class="token keyword">set</span> 列名 <span class="token operator">=</span> 修改值 <span class="token keyword">where</span> id <span class="token operator">in</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="dql-查询" tabindex="-1"><a class="header-anchor" href="#dql-查询" aria-hidden="true">#</a> DQL: 查询</h3><h4 id="基本查询-瑟莱特" tabindex="-1"><a class="header-anchor" href="#基本查询-瑟莱特" aria-hidden="true">#</a> 基本查询 | 瑟莱特</h4><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 语法  select 列名...... from 表名 where 条件</span>
<span class="token comment">-- 查询所有信息(*代表所有列)</span>
	<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表名<span class="token punctuation">;</span>
<span class="token comment">-- 查询时更改列名(显示作用)</span>
	<span class="token keyword">select</span> 列名 <span class="token keyword">as</span> <span class="token string">&#39;新名字&#39;</span> <span class="token keyword">from</span> 表名<span class="token punctuation">;</span>
<span class="token comment">-- 查询列值的范围</span>
	<span class="token keyword">select</span> 列名<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">from</span> 表名 <span class="token keyword">where</span> 列名 <span class="token operator">between</span> 最小值 <span class="token operator">and</span> 最大值<span class="token punctuation">;</span>
	<span class="token keyword">select</span> 列名<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">from</span> 表名 <span class="token keyword">where</span> 列名 <span class="token operator">&gt;=</span> 最小值 <span class="token operator">and</span> 列名 <span class="token operator">&lt;=</span> 最大值<span class="token punctuation">;</span>
<span class="token comment">-- 查询多行列值</span>
	<span class="token keyword">select</span> 列名<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">from</span> 表名 <span class="token keyword">where</span> 列名 <span class="token operator">in</span><span class="token punctuation">(</span>列值<span class="token number">1</span><span class="token punctuation">,</span>列值<span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>    <span class="token operator">in</span> 相当于多个 <span class="token operator">or</span>
<span class="token comment">-- 查询包含/开头/结尾</span>
	<span class="token keyword">select</span> 列名<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">from</span> 表名 <span class="token keyword">where</span> 列名 <span class="token operator">like</span> <span class="token string">&#39;%值%&#39;</span> <span class="token operator">/</span> 值<span class="token operator">%</span> <span class="token operator">/</span> <span class="token operator">%</span>值<span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="关联查询" tabindex="-1"><a class="header-anchor" href="#关联查询" aria-hidden="true">#</a> 关联查询</h4><h5 id="内连接" tabindex="-1"><a class="header-anchor" href="#内连接" aria-hidden="true">#</a> 内连接</h5><blockquote><p><strong>特点:</strong> 只会查询两张表重和的部分 其余的则不会查询出来</p></blockquote><p><strong>语法:</strong></p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 内</span>
	语法 <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表<span class="token number">1</span><span class="token punctuation">,</span>表<span class="token number">2</span> <span class="token keyword">where</span> 表<span class="token number">1.</span>外键<span class="token operator">=</span>表<span class="token number">2.</span>主键
	语法 <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表<span class="token number">1</span> <span class="token keyword">inner</span> <span class="token keyword">join</span> 表<span class="token number">2</span> <span class="token keyword">on</span> 表<span class="token number">1.</span>外键<span class="token operator">=</span>表<span class="token number">2.</span>主键
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><img src="https://apaiimages.oss-cn-guangzhou.aliyuncs.com/MD/image-20220819125858951.png" alt="image-20220819130836662"></p><h5 id="外-左连接" tabindex="-1"><a class="header-anchor" href="#外-左连接" aria-hidden="true">#</a> 外/左连接</h5><blockquote><p><strong>特点:</strong> 以左表为主全部查询出来 如果右表部分没数据则以null</p></blockquote><p><strong>语法:</strong></p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 外</span>
	<span class="token comment">-- 左外连接(以左表为主表,会把左表数据全部查询出来)</span>
	语法 <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表<span class="token number">1</span> <span class="token keyword">left</span> <span class="token keyword">join</span> 表<span class="token number">2</span> <span class="token keyword">on</span> 表<span class="token number">1.</span>主键<span class="token operator">=</span>表<span class="token number">2.</span>外键
	语法 <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表<span class="token number">1</span> <span class="token keyword">left</span> <span class="token keyword">outer</span> <span class="token keyword">join</span> 表<span class="token number">2</span> <span class="token keyword">on</span> 表<span class="token number">1.</span>主键<span class="token operator">=</span>表<span class="token number">2.</span>外键
	
<span class="token comment">-- 配合 表2 的外键为 null 可 左表且不重合的数据</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表<span class="token number">1</span> <span class="token keyword">left</span> <span class="token keyword">outer</span> <span class="token keyword">join</span> 表<span class="token number">2</span> <span class="token keyword">on</span> 表<span class="token number">1.</span>主键<span class="token operator">=</span>表<span class="token number">2.</span>外键 <span class="token keyword">where</span> 表<span class="token number">2.</span>外键 <span class="token operator">=</span> <span class="token boolean">null</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><img src="https://apaiimages.oss-cn-guangzhou.aliyuncs.com/MD/image-20220819130316462.png" alt="image-20220819130316462" style="zoom:150%;"><h5 id="外-右连接" tabindex="-1"><a class="header-anchor" href="#外-右连接" aria-hidden="true">#</a> 外/右连接</h5><blockquote><p><strong>特点:</strong> 以右表为主全部查询出来 如果左表部分没数据则以null</p></blockquote><p><strong>语法:</strong></p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 外</span>
	<span class="token comment">-- 右外连接(以右表为主表,会把右表数据全部查询出来)</span>
	语法 <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表<span class="token number">1</span> <span class="token keyword">right</span> <span class="token keyword">join</span> 表<span class="token number">2</span> <span class="token keyword">on</span> 表<span class="token number">1.</span>主键<span class="token operator">=</span>表<span class="token number">2.</span>外键
	语法 <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表<span class="token number">1</span> <span class="token keyword">right</span> <span class="token keyword">outer</span> <span class="token keyword">join</span> 表<span class="token number">2</span> <span class="token keyword">on</span> 表<span class="token number">1.</span>主键<span class="token operator">=</span>表<span class="token number">2.</span>外键
	
<span class="token comment">-- 配合 表2 的外键为 null 可 左表且不重合的数据</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表<span class="token number">1</span> <span class="token keyword">left</span> <span class="token keyword">outer</span> <span class="token keyword">join</span> 表<span class="token number">2</span> <span class="token keyword">on</span> 表<span class="token number">1.</span>主键<span class="token operator">=</span>表<span class="token number">2.</span>外键 <span class="token keyword">where</span> 表<span class="token number">1.</span>外键 <span class="token operator">=</span> <span class="token boolean">null</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><img src="https://apaiimages.oss-cn-guangzhou.aliyuncs.com/MD/image-20220819130443814.png" alt="image-20220819130443814"></p><h5 id="外连接-多表查询" tabindex="-1"><a class="header-anchor" href="#外连接-多表查询" aria-hidden="true">#</a> 外连接 多表查询</h5><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 多表连接</span>
	<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表<span class="token number">1</span><span class="token punctuation">,</span>表<span class="token number">2</span><span class="token punctuation">,</span>表<span class="token number">3</span> <span class="token keyword">where</span> 表<span class="token number">1.</span>外键<span class="token operator">=</span>表<span class="token number">2.</span>主键 <span class="token operator">and</span> 表<span class="token number">3.</span>主键<span class="token operator">=</span>表<span class="token number">2.</span>外键<span class="token punctuation">;</span>
	<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表<span class="token number">1</span> <span class="token keyword">left</span> <span class="token keyword">join</span> 表<span class="token number">2</span> <span class="token keyword">on</span> 表<span class="token number">1.</span>主键<span class="token operator">=</span>表<span class="token number">2.</span>外键 <span class="token keyword">left</span> <span class="token keyword">join</span> 表<span class="token number">3</span> <span class="token keyword">on</span> 表<span class="token number">3.</span>主键<span class="token operator">=</span>表<span class="token number">2.</span>外键<span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h5 id="全外查询" tabindex="-1"><a class="header-anchor" href="#全外查询" aria-hidden="true">#</a> 全外查询</h5><blockquote><p><strong>特点:</strong> 以两张表为主 查询所有的数据 为空的以null填充</p></blockquote><p><strong>语法:</strong></p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表<span class="token number">1</span> <span class="token keyword">left</span> <span class="token keyword">outer</span> <span class="token keyword">join</span> 表<span class="token number">2</span> <span class="token keyword">on</span> 表<span class="token number">1.</span>主键<span class="token operator">=</span>表<span class="token number">2.</span>外键
<span class="token keyword">union</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表<span class="token number">1</span> <span class="token keyword">right</span> <span class="token keyword">outer</span> <span class="token keyword">join</span> 表<span class="token number">2</span> <span class="token keyword">on</span> 表<span class="token number">1.</span>主键<span class="token operator">=</span>表<span class="token number">2.</span>外键
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><img src="https://apaiimages.oss-cn-guangzhou.aliyuncs.com/MD/image-20220819131519212.png" alt="image-20220819131519212"></p><h5 id="全外-去除内连接" tabindex="-1"><a class="header-anchor" href="#全外-去除内连接" aria-hidden="true">#</a> 全外(去除内连接)</h5><blockquote><p><strong>特点:</strong> 以两张表为主 查询所有的数据 但会去除内连接查询的重合数据 为空的以null填充</p></blockquote><p><strong>语法:</strong></p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表<span class="token number">1</span> <span class="token keyword">left</span> <span class="token keyword">outer</span> <span class="token keyword">join</span> 表<span class="token number">2</span> <span class="token keyword">on</span> 表<span class="token number">1.</span>主键<span class="token operator">=</span>表<span class="token number">2.</span>外键
<span class="token keyword">where</span> 表<span class="token number">2.</span>外键 <span class="token operator">is</span> <span class="token boolean">null</span>
<span class="token keyword">union</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表<span class="token number">1</span> <span class="token keyword">right</span> <span class="token keyword">outer</span> <span class="token keyword">join</span> 表<span class="token number">2</span> <span class="token keyword">on</span> 表<span class="token number">1.</span>主键<span class="token operator">=</span>表<span class="token number">2.</span>外键
<span class="token keyword">where</span> 表<span class="token number">1.</span>主键 <span class="token operator">is</span> <span class="token boolean">null</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><img src="https://apaiimages.oss-cn-guangzhou.aliyuncs.com/MD/image-20220819150605075.png" alt="image-20220819150605075"></p><h2 id="特殊条件值" tabindex="-1"><a class="header-anchor" href="#特殊条件值" aria-hidden="true">#</a> 特殊条件值</h2><h3 id="运算符-判断" tabindex="-1"><a class="header-anchor" href="#运算符-判断" aria-hidden="true">#</a> 运算符 - 判断</h3><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 算术运算符 </span>
	<span class="token operator">+</span> <span class="token operator">-</span> <span class="token operator">*</span> <span class="token operator">/</span> <span class="token operator">%</span>
	
<span class="token comment">-- 关系运算符</span>
	<span class="token operator">&gt;</span> <span class="token operator">&gt;=</span>  <span class="token operator">&lt;</span> <span class="token operator">&lt;=</span>  <span class="token operator">=</span>  <span class="token operator">!=</span>  <span class="token comment">--关系运算符的结果为真(用数字1表示)或者假(用数字0表示假)</span>
	
<span class="token comment">-- 范围的首和尾都包含</span>
	<span class="token keyword">select</span> 包含值 <span class="token operator">between</span> 最小值 <span class="token operator">and</span> 最大值
	<span class="token operator">like</span>  <span class="token operator">-</span> <span class="token operator">%</span>        <span class="token comment">--  为通配符,任意内容</span>
	<span class="token comment">-- 指定字符开头</span>
		<span class="token keyword">select</span> <span class="token string">&#39;你是我的月亮&#39;</span> <span class="token operator">like</span> <span class="token string">&#39;你%&#39;</span> <span class="token punctuation">;</span>
	<span class="token comment">-- 指定字符结尾</span>
		<span class="token keyword">select</span> <span class="token string">&#39;你是我的月亮&#39;</span> <span class="token operator">like</span> <span class="token string">&#39;%月亮&#39;</span> <span class="token punctuation">;</span>
	<span class="token comment">-- 包含指定字符</span>
		<span class="token keyword">select</span> <span class="token string">&#39;你是我的月亮&#39;</span> <span class="token operator">like</span> <span class="token string">&#39;%我的%&#39;</span> <span class="token punctuation">;</span>
	<span class="token comment">-- 模糊 多个</span>
		<span class="token keyword">select</span> <span class="token string">&#39;你是我的月亮&#39;</span> <span class="token operator">like</span> <span class="token string">&#39;%我, 月%&#39;</span> <span class="token punctuation">;</span>
		<span class="token keyword">select</span> <span class="token string">&#39;你是我的月亮&#39;</span> <span class="token operator">like</span> <span class="token string">&#39;%我%, %月%&#39;</span> <span class="token punctuation">;</span>
		<span class="token keyword">select</span> <span class="token string">&#39;你是我的月亮&#39;</span> <span class="token operator">like</span> <span class="token string">&#39;%我%月%&#39;</span> <span class="token punctuation">;</span>
		
<span class="token comment">-- 空判断</span>
	<span class="token keyword">select</span>    <span class="token string">&#39;  &#39;</span> <span class="token operator">=</span> <span class="token string">&#39;  &#39;</span> <span class="token punctuation">;</span>
	<span class="token keyword">select</span> <span class="token boolean">null</span> <span class="token operator">is</span> <span class="token boolean">null</span><span class="token punctuation">;</span>
			列名 <span class="token operator">is</span> <span class="token boolean">null</span>
			列名 <span class="token operator">is</span> <span class="token operator">not</span> <span class="token boolean">null</span>
			
<span class="token comment">-- 逻辑运算符 结果：真（1） 假（0）</span>
	非 <span class="token operator">not</span>     <span class="token keyword">select</span> <span class="token operator">not</span> <span class="token number">1</span><span class="token punctuation">;</span>
	与 <span class="token operator">and</span>     <span class="token keyword">select</span> <span class="token number">1</span> <span class="token operator">and</span> <span class="token number">1</span><span class="token punctuation">;</span>
	或 <span class="token operator">or</span>      <span class="token keyword">select</span> <span class="token number">0</span> <span class="token operator">or</span> <span class="token number">0</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="分页-分组" tabindex="-1"><a class="header-anchor" href="#分页-分组" aria-hidden="true">#</a> 分页 -- 分组</h3><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 分页 | 可控制查询的条数</span>
	<span class="token comment">-- limit 后面为一个值n时,表示从第一行开始-查询n行</span>
		语法: <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表名 <span class="token keyword">limit</span> n<span class="token punctuation">;</span>
	<span class="token comment">-- limit 后面为两个值m,n时,表示从m+1行开始-查询n行</span>
		语法: <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表名 <span class="token keyword">limit</span> m<span class="token punctuation">,</span>n<span class="token punctuation">;</span>
	<span class="token comment">-- 页码-pageNum 每页显示的行数-pageSize 表示</span>
		<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表名 <span class="token keyword">limit</span> <span class="token punctuation">(</span>pageNum<span class="token operator">-</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token operator">*</span>pageSize<span class="token punctuation">,</span>pageSize<span class="token punctuation">;</span>
		
<span class="token comment">-- 聚合函数</span>
	<span class="token comment">-- avg-平均数 count-统计行数 max/min-最大/小值 sum-和</span>
	语法: <span class="token keyword">select</span>  函数<span class="token punctuation">(</span> 列名 <span class="token punctuation">)</span>  <span class="token keyword">from</span> 表名<span class="token punctuation">;</span>  <span class="token comment">-- 在使用函数时查询列名报错 any_value(列名)		</span>
		
<span class="token comment">-- 分组</span>
	语法: <span class="token keyword">select</span> <span class="token punctuation">(</span>列名<span class="token operator">-</span>显示作用 函数<span class="token punctuation">)</span> <span class="token keyword">from</span> 表名 <span class="token keyword">group</span> <span class="token keyword">by</span> 分组列名 <span class="token keyword">having</span> 条件<span class="token punctuation">;</span>
	<span class="token keyword">group</span> <span class="token keyword">by</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">having</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>  <span class="token comment">-- 分组的语法通常需要跟聚合函数一起用</span>
<span class="token comment">-- 多字段分组</span>
	语法: <span class="token keyword">select</span> <span class="token punctuation">(</span>列名<span class="token operator">-</span>显示作用 函数<span class="token punctuation">)</span> <span class="token keyword">from</span> 表名 <span class="token keyword">group</span> <span class="token keyword">by</span> 分组列名<span class="token number">1</span><span class="token punctuation">,</span> 列明<span class="token number">2.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">having</span> 条件<span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="条件-基本聚合函数" tabindex="-1"><a class="header-anchor" href="#条件-基本聚合函数" aria-hidden="true">#</a> 条件 基本聚合函数</h3><blockquote><p>基本聚合函数</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 可配合分组使用 聚合函数</span>
<span class="token comment">-- 在使用函数时查询列名报错 any_value(列名)</span>
avg<span class="token operator">-</span>平均数 <span class="token operator">|</span> count<span class="token operator">-</span>统计行数 <span class="token operator">|</span> max<span class="token operator">/</span>min<span class="token operator">-</span>最大<span class="token operator">/</span>小值 <span class="token operator">|</span> sum<span class="token operator">-</span>和

<span class="token comment">-- 聚合函数可进行条件判断</span>
max<span class="token operator">/</span>min   最大<span class="token operator">/</span>小值   条件 真<span class="token operator">-</span><span class="token number">1</span> <span class="token operator">/</span> 假<span class="token operator">-</span><span class="token number">0</span>
sum        和<span class="token operator">-</span>相加    条件 真<span class="token operator">-</span><span class="token number">1</span> <span class="token operator">/</span> 假<span class="token operator">-</span><span class="token number">0</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><p>条件聚合函数</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 表示 该列如果小于值 则 +1 否则 +0 最后统计总数</span>
<span class="token function">sum</span><span class="token punctuation">(</span><span class="token keyword">case</span> <span class="token keyword">when</span> 列名 <span class="token operator">&lt;</span> 值 <span class="token keyword">then</span> <span class="token number">1</span> <span class="token keyword">else</span> <span class="token number">0</span> <span class="token keyword">end</span><span class="token punctuation">)</span> 列别名
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="排序" tabindex="-1"><a class="header-anchor" href="#排序" aria-hidden="true">#</a> 排序</h3><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 排序</span>
	<span class="token comment">-- order by ... asc升序（默认）  desc降序</span>
	<span class="token comment">-- 升序-以指定列名升序排序 -- asc可以省略</span>
		语法: <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表名  <span class="token keyword">order</span> <span class="token keyword">by</span> 列名 <span class="token keyword">asc</span><span class="token punctuation">;</span>
	<span class="token comment">-- 降序-以指定列名降序排序 -- desc降序</span>
		语法: <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表名  <span class="token keyword">order</span> <span class="token keyword">by</span> 列名 <span class="token keyword">desc</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="mysql-特殊函数" tabindex="-1"><a class="header-anchor" href="#mysql-特殊函数" aria-hidden="true">#</a> MySql 特殊函数</h2><h3 id="时间类函数" tabindex="-1"><a class="header-anchor" href="#时间类函数" aria-hidden="true">#</a> 时间类函数</h3><h4 id="获取现在时间函数" tabindex="-1"><a class="header-anchor" href="#获取现在时间函数" aria-hidden="true">#</a> 获取现在时间函数</h4><p>详见: https://www.runoob.com/sql/sql-dates.html</p><table><thead><tr><th><a href="https://www.runoob.com/sql/func-now.html" target="_blank" rel="noopener noreferrer">NOW()<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></th><th>返回当前的日期和时间</th></tr></thead><tbody><tr><td><a href="https://www.runoob.com/sql/func-curdate.html" target="_blank" rel="noopener noreferrer">CURDATE()<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></td><td>返回当前的日期</td></tr><tr><td><a href="https://www.runoob.com/sql/func-curtime.html" target="_blank" rel="noopener noreferrer">CURTIME()<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></td><td>返回当前的时间</td></tr><tr><td><a href="https://www.runoob.com/sql/func-date.html" target="_blank" rel="noopener noreferrer">DATE()<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></td><td>提取日期或日期/时间表达式的日期部分</td></tr><tr><td><a href="https://www.runoob.com/sql/func-extract.html" target="_blank" rel="noopener noreferrer">EXTRACT()<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></td><td>返回日期/时间的单独部分</td></tr><tr><td><a href="https://www.runoob.com/sql/func-date-add.html" target="_blank" rel="noopener noreferrer">DATE_ADD()<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></td><td>向日期添加指定的时间间隔</td></tr><tr><td><a href="https://www.runoob.com/sql/func-date-sub.html" target="_blank" rel="noopener noreferrer">DATE_SUB()<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></td><td>从日期减去指定的时间间隔</td></tr><tr><td><a href="https://www.runoob.com/sql/func-datediff-mysql.html" target="_blank" rel="noopener noreferrer">DATEDIFF()<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></td><td>返回两个日期之间的天数</td></tr><tr><td><a href="https://www.runoob.com/sql/func-date-format.html" target="_blank" rel="noopener noreferrer">DATE_FORMAT()<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></td><td>用不同的格式显示日期/时间</td></tr></tbody></table><h4 id="时间差列" tabindex="-1"><a class="header-anchor" href="#时间差列" aria-hidden="true">#</a> 时间差列</h4><blockquote><p>可使用 timediff | 时间差 函数计算出两个时间的时间差为新的一列数据</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> timediff<span class="token punctuation">(</span><span class="token string">&quot;时间1&quot;</span> <span class="token punctuation">,</span> <span class="token string">&quot;时间2&quot;</span><span class="token punctuation">)</span> <span class="token keyword">from</span> 表名
<span class="token keyword">select</span> timediff<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">,</span> seckill_endtime<span class="token punctuation">)</span> <span class="token keyword">from</span> seckill

<span class="token operator">-</span><span class="token number">63</span>:<span class="token number">13</span>:<span class="token number">46</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="时间差条件" tabindex="-1"><a class="header-anchor" href="#时间差条件" aria-hidden="true">#</a> 时间差条件</h4><p><a href="https://blog.csdn.net/homelam/article/details/88647099" target="_blank" rel="noopener noreferrer">Mysql日期差函数，Mysql选择两个日期字段相差大于或小于一定时间_mysql 时间间隔大于<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></p><blockquote><p>FROM_UNIXTIME | 语法</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> tpaylog <span class="token keyword">where</span> TIMESTAMPDIFF<span class="token punctuation">(</span>时间差类型 <span class="token punctuation">,</span>时间<span class="token number">2</span><span class="token punctuation">,</span> 时间<span class="token number">1</span> <span class="token operator">&gt;</span> <span class="token number">30</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><blockquote><p>时间差类型</p></blockquote><ul><li><p>FRAC_SECOND - 表示间隔是毫秒</p></li><li><p>SECOND - 秒</p></li><li><p>MINUTE - 分钟</p></li><li><p>HOUR - 小时</p></li><li><p>DAY - 天</p></li><li><p>WEEK - 星期</p></li><li><p>MONTH - 月</p></li><li><p>QUARTER - 季度</p></li><li><p>YEAR - 年</p></li></ul><blockquote><p>案例</p><p>https://blog.csdn.net/m0_67394002/article/details/126113579</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 现在的时间 至 该字段的时间 差值 大于30分钟</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> tpaylog <span class="token keyword">where</span> TIMESTAMPDIFF<span class="token punctuation">(</span><span class="token keyword">MINUTE</span> <span class="token punctuation">,</span>tpaylog_ordertime<span class="token punctuation">,</span> <span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">&gt;</span> <span class="token number">30</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> tpaylog <span class="token keyword">where</span> TIMESTAMPDIFF<span class="token punctuation">(</span>时间差类型 <span class="token punctuation">,</span>时间字段<span class="token number">1</span><span class="token punctuation">,</span> 时间字段<span class="token number">2</span><span class="token punctuation">)</span> <span class="token operator">&gt;</span> 值

<span class="token comment">-- 类似函数 时间之差大于31天 | datediff(now(),时间字段) &gt;= 31</span>
datediff<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span>busi_date<span class="token punctuation">)</span> <span class="token operator">&gt;=</span> <span class="token number">31</span>

<span class="token comment">-- 类似函数 时间之差小于于7天 | 时间字段 &gt; DATE_SUB(now(),INTERVAL 7 时间单位);</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token identifier"><span class="token punctuation">`</span>stock_info<span class="token punctuation">`</span></span> <span class="token keyword">where</span>
busi_date <span class="token operator">&gt;</span> DATE_SUB<span class="token punctuation">(</span><span class="token string">&#39;2022-11-11&#39;</span><span class="token punctuation">,</span><span class="token keyword">INTERVAL</span> <span class="token number">7</span> <span class="token keyword">day</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="时间转换格式" tabindex="-1"><a class="header-anchor" href="#时间转换格式" aria-hidden="true">#</a> 时间转换格式</h4><p>详见: https://www.runoob.com/sql/func-date-format.html</p><div class="language-java line-numbers-mode" data-ext="java"><pre class="language-java"><code><span class="token comment">// date_format(时间字段, 时间格式) = 条件值</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><div class="language-java line-numbers-mode" data-ext="java"><pre class="language-java"><code><span class="token comment">// busi_date 时间字段 | 如: 2022-11-04 --&gt; 2022-11</span>
select <span class="token operator">*</span> from stock_in_order where
    audit_status <span class="token operator">&gt;</span> <span class="token number">2</span> and category <span class="token operator">=</span> <span class="token number">2</span> and company_id <span class="token operator">=</span> <span class="token number">37</span> and status <span class="token operator">=</span> <span class="token number">1</span>
    and <span class="token function">date_format</span><span class="token punctuation">(</span>busi_date<span class="token punctuation">,</span> <span class="token char">&#39;%Y-%m&#39;</span><span class="token punctuation">)</span> <span class="token operator">=</span> &#39;<span class="token number">2022</span><span class="token operator">-</span><span class="token number">10</span>&#39;

<span class="token operator">&lt;</span><span class="token keyword">if</span> test<span class="token operator">=</span><span class="token string">&quot;entity.start != null and entity.start != &#39;&#39;&quot;</span><span class="token operator">&gt;</span>
    and <span class="token function">date_format</span><span class="token punctuation">(</span>a<span class="token punctuation">.</span>create_time<span class="token punctuation">,</span>&#39;<span class="token operator">%</span><span class="token class-name">Y</span><span class="token operator">-</span><span class="token operator">%</span>m<span class="token operator">-</span><span class="token operator">%</span>d&#39;<span class="token punctuation">)</span> <span class="token operator">&amp;</span>gt<span class="token punctuation">;</span><span class="token operator">=</span> #<span class="token punctuation">{</span>entity<span class="token punctuation">.</span>start<span class="token punctuation">}</span>
<span class="token operator">&lt;</span><span class="token operator">/</span><span class="token keyword">if</span><span class="token operator">&gt;</span>
 <span class="token operator">&lt;</span><span class="token keyword">if</span> test<span class="token operator">=</span><span class="token string">&quot;entity.end != null and entity.end != &#39;&#39;&quot;</span><span class="token operator">&gt;</span>
    and <span class="token function">date_format</span><span class="token punctuation">(</span>a<span class="token punctuation">.</span>create_time<span class="token punctuation">,</span>&#39;<span class="token operator">%</span><span class="token class-name">Y</span><span class="token operator">-</span><span class="token operator">%</span>m<span class="token operator">-</span><span class="token operator">%</span>d&#39;<span class="token punctuation">)</span> <span class="token operator">&amp;</span>lt<span class="token punctuation">;</span><span class="token operator">=</span> #<span class="token punctuation">{</span>entity<span class="token punctuation">.</span>end<span class="token punctuation">}</span>
<span class="token operator">&lt;</span><span class="token operator">/</span><span class="token keyword">if</span><span class="token operator">&gt;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="查询时间偏移数据" tabindex="-1"><a class="header-anchor" href="#查询时间偏移数据" aria-hidden="true">#</a> 查询时间偏移数据</h4><p>详见: https://blog.csdn.net/weixin_49071539/article/details/116458017</p><p>​ https://www.cnblogs.com/wjw1014/p/12917335.html</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 今天</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表名 <span class="token keyword">where</span> to_days<span class="token punctuation">(</span>时间字段名<span class="token punctuation">)</span> <span class="token operator">=</span> to_days<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 昨天</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> 表名 <span class="token keyword">WHERE</span> TO_DAYS<span class="token punctuation">(</span> <span class="token function">NOW</span><span class="token punctuation">(</span> <span class="token punctuation">)</span> <span class="token punctuation">)</span> <span class="token operator">-</span> TO_DAYS<span class="token punctuation">(</span> 时间字段名<span class="token punctuation">)</span> <span class="token operator">&lt;=</span> <span class="token number">1</span><span class="token punctuation">;</span>

<span class="token comment">-- 7天</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> 表名 <span class="token keyword">where</span> DATE_SUB<span class="token punctuation">(</span>CURDATE<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">INTERVAL</span> <span class="token number">7</span> <span class="token keyword">DAY</span><span class="token punctuation">)</span> <span class="token operator">&lt;=</span> <span class="token keyword">date</span><span class="token punctuation">(</span>时间字段名<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 近30天</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> 表名 <span class="token keyword">where</span> DATE_SUB<span class="token punctuation">(</span>CURDATE<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">INTERVAL</span> <span class="token number">30</span> <span class="token keyword">DAY</span><span class="token punctuation">)</span> <span class="token operator">&lt;=</span> <span class="token keyword">date</span><span class="token punctuation">(</span>时间字段名<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 本月</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> 表名 <span class="token keyword">WHERE</span> DATE_FORMAT<span class="token punctuation">(</span> 时间字段名<span class="token punctuation">,</span> <span class="token string">&#39;%Y%m&#39;</span> <span class="token punctuation">)</span> <span class="token operator">=</span> DATE_FORMAT<span class="token punctuation">(</span> CURDATE<span class="token punctuation">(</span> <span class="token punctuation">)</span> <span class="token punctuation">,</span> <span class="token string">&#39;%Y%m&#39;</span> <span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 上一月 | 使用now()可用 字符串时间字段&#39;2022-10-1&#39;可用</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> 表名 <span class="token keyword">WHERE</span> PERIOD_DIFF<span class="token punctuation">(</span> date_format<span class="token punctuation">(</span> <span class="token function">now</span><span class="token punctuation">(</span> <span class="token punctuation">)</span> <span class="token punctuation">,</span> <span class="token string">&#39;%Y%m&#39;</span> <span class="token punctuation">)</span> <span class="token punctuation">,</span> date_format<span class="token punctuation">(</span> 时间字段名<span class="token punctuation">,</span> <span class="token string">&#39;%Y%m&#39;</span> <span class="token punctuation">)</span> <span class="token punctuation">)</span> <span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询本季度数据</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> <span class="token identifier"><span class="token punctuation">`</span>ht_invoice_information<span class="token punctuation">`</span></span> <span class="token keyword">where</span> QUARTER<span class="token punctuation">(</span>create_date<span class="token punctuation">)</span><span class="token operator">=</span>QUARTER<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询上季度数据</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> <span class="token identifier"><span class="token punctuation">`</span>ht_invoice_information<span class="token punctuation">`</span></span> <span class="token keyword">where</span> QUARTER<span class="token punctuation">(</span>create_date<span class="token punctuation">)</span><span class="token operator">=</span>QUARTER<span class="token punctuation">(</span>DATE_SUB<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">interval</span> <span class="token number">1</span> QUARTER<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询本年数据</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> <span class="token identifier"><span class="token punctuation">`</span>ht_invoice_information<span class="token punctuation">`</span></span> <span class="token keyword">where</span> <span class="token keyword">YEAR</span><span class="token punctuation">(</span>create_date<span class="token punctuation">)</span><span class="token operator">=</span><span class="token keyword">YEAR</span><span class="token punctuation">(</span><span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询上年数据</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> <span class="token identifier"><span class="token punctuation">`</span>ht_invoice_information<span class="token punctuation">`</span></span> <span class="token keyword">where</span> <span class="token keyword">year</span><span class="token punctuation">(</span>create_date<span class="token punctuation">)</span><span class="token operator">=</span><span class="token keyword">year</span><span class="token punctuation">(</span>date_sub<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">interval</span> <span class="token number">1</span> <span class="token keyword">year</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询当前这周的数据</span>
<span class="token keyword">SELECT</span> name<span class="token punctuation">,</span>submittime <span class="token keyword">FROM</span> enterprise <span class="token keyword">WHERE</span> YEARWEEK<span class="token punctuation">(</span>date_format<span class="token punctuation">(</span>submittime<span class="token punctuation">,</span><span class="token string">&#39;%Y-%m-%d&#39;</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">=</span> YEARWEEK<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询上周的数据</span>
<span class="token keyword">SELECT</span> name<span class="token punctuation">,</span>submittime <span class="token keyword">FROM</span> enterprise <span class="token keyword">WHERE</span> YEARWEEK<span class="token punctuation">(</span>date_format<span class="token punctuation">(</span>submittime<span class="token punctuation">,</span><span class="token string">&#39;%Y-%m-%d&#39;</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">=</span> YEARWEEK<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token number">1</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询当前月份的数据</span>
<span class="token keyword">select</span> name<span class="token punctuation">,</span>submittime <span class="token keyword">from</span> enterprise   <span class="token keyword">where</span> date_format<span class="token punctuation">(</span>submittime<span class="token punctuation">,</span><span class="token string">&#39;%Y-%m&#39;</span><span class="token punctuation">)</span><span class="token operator">=</span>date_format<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token string">&#39;%Y-%m&#39;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询距离当前现在6个月的数据</span>
<span class="token keyword">select</span> name<span class="token punctuation">,</span>submittime <span class="token keyword">from</span> enterprise <span class="token keyword">where</span> submittime <span class="token operator">between</span> date_sub<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">interval</span> <span class="token number">6</span> <span class="token keyword">month</span><span class="token punctuation">)</span> <span class="token operator">and</span> <span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
​<span class="token identifier"><span class="token punctuation">`</span><span class="token punctuation">`</span><span class="token punctuation">`</span>

-- 近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) &lt;= date(时间字段名);

-- 本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, &#39;%Y%m&#39; ) = DATE_FORMAT( CURDATE( ) , &#39;%Y%m&#39; );

-- 上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , &#39;%Y%m&#39; ) , date_format( 时间字段名, &#39;%Y%m&#39; ) ) =1;

-- 查询本季度数据
select * from <span class="token punctuation">`</span></span>ht_invoice_information<span class="token identifier"><span class="token punctuation">`</span> where QUARTER(create_date)=QUARTER(now());

-- 查询上季度数据
select * from <span class="token punctuation">`</span></span>ht_invoice_information<span class="token identifier"><span class="token punctuation">`</span> where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

-- 查询本年数据
select * from <span class="token punctuation">`</span></span>ht_invoice_information<span class="token identifier"><span class="token punctuation">`</span> where YEAR(create_date)=YEAR(NOW());

-- 查询上年数据
select * from <span class="token punctuation">`</span></span>ht_invoice_information<span class="token punctuation">`</span> <span class="token keyword">where</span> <span class="token keyword">year</span><span class="token punctuation">(</span>create_date<span class="token punctuation">)</span><span class="token operator">=</span><span class="token keyword">year</span><span class="token punctuation">(</span>date_sub<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">interval</span> <span class="token number">1</span> <span class="token keyword">year</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询当前这周的数据</span>
<span class="token keyword">SELECT</span> name<span class="token punctuation">,</span>submittime <span class="token keyword">FROM</span> enterprise <span class="token keyword">WHERE</span> YEARWEEK<span class="token punctuation">(</span>date_format<span class="token punctuation">(</span>submittime<span class="token punctuation">,</span><span class="token string">&#39;%Y-%m-%d&#39;</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">=</span> YEARWEEK<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询上周的数据</span>
<span class="token keyword">SELECT</span> name<span class="token punctuation">,</span>submittime <span class="token keyword">FROM</span> enterprise <span class="token keyword">WHERE</span> YEARWEEK<span class="token punctuation">(</span>date_format<span class="token punctuation">(</span>submittime<span class="token punctuation">,</span><span class="token string">&#39;%Y-%m-%d&#39;</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">=</span> YEARWEEK<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token number">1</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询当前月份的数据</span>
<span class="token keyword">select</span> name<span class="token punctuation">,</span>submittime <span class="token keyword">from</span> enterprise   <span class="token keyword">where</span> date_format<span class="token punctuation">(</span>submittime<span class="token punctuation">,</span><span class="token string">&#39;%Y-%m&#39;</span><span class="token punctuation">)</span><span class="token operator">=</span>date_format<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token string">&#39;%Y-%m&#39;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 查询距离当前现在6个月的数据</span>
<span class="token keyword">select</span> name<span class="token punctuation">,</span>submittime <span class="token keyword">from</span> enterprise <span class="token keyword">where</span> submittime <span class="token operator">between</span> date_sub<span class="token punctuation">(</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">interval</span> <span class="token number">6</span> <span class="token keyword">month</span><span class="token punctuation">)</span> <span class="token operator">and</span> <span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="数学函数" tabindex="-1"><a class="header-anchor" href="#数学函数" aria-hidden="true">#</a> 数学函数</h3><p>https://blog.csdn.net/qq_43842093/article/details/120938688</p><h4 id="绝对值" tabindex="-1"><a class="header-anchor" href="#绝对值" aria-hidden="true">#</a> 绝对值</h4><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- ABS(数值字段) 绝对值</span>
<span class="token keyword">SELECT</span> ABS<span class="token punctuation">(</span><span class="token operator">-</span><span class="token number">33</span><span class="token punctuation">)</span> <span class="token comment">-- 输出: 字段是绝对值 没有负数 33</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="余数" tabindex="-1"><a class="header-anchor" href="#余数" aria-hidden="true">#</a> 余数</h4><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- MOD(N,M) 或%:返回N被M除的余数</span>
<span class="token keyword">SELECT</span> <span class="token function">MOD</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token comment">-- 输出余数 1</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="不小于最小整数值" tabindex="-1"><a class="header-anchor" href="#不小于最小整数值" aria-hidden="true">#</a> 不小于最小整数值</h4><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- CEILING(X) 返回不小于X的最小整数值。</span>
<span class="token keyword">select</span> CEILING<span class="token punctuation">(</span><span class="token number">1.23</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 输出余数 2</span>
<span class="token keyword">select</span> CEILING<span class="token punctuation">(</span><span class="token operator">-</span><span class="token number">1.23</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 输出余数 -1</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="四舍五入的整数" tabindex="-1"><a class="header-anchor" href="#四舍五入的整数" aria-hidden="true">#</a> 四舍五入的整数</h4><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- ROUND(X) :返回参数X的四舍五入的一个整数。</span>
<span class="token keyword">select</span> <span class="token function">ROUND</span><span class="token punctuation">(</span><span class="token number">1.58</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 输出余数 2</span>
<span class="token keyword">select</span> <span class="token function">ROUND</span><span class="token punctuation">(</span><span class="token operator">-</span><span class="token number">1.58</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 输出余数 -2</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="字符串函数" tabindex="-1"><a class="header-anchor" href="#字符串函数" aria-hidden="true">#</a> 字符串函数</h3><h4 id="ascii代码值" tabindex="-1"><a class="header-anchor" href="#ascii代码值" aria-hidden="true">#</a> ASCII代码值</h4><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- ASCII(str):返回字符串str的最左面字符的ASCII代码值。</span>
<span class="token comment">-- 如果str是空字符串，返回0。如果str是NULL，返回NULL。</span>
<span class="token keyword">select</span> ASCII<span class="token punctuation">(</span><span class="token boolean">null</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- null</span>
<span class="token keyword">select</span> ASCII<span class="token punctuation">(</span><span class="token string">&#39;&#39;</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 0</span>
<span class="token keyword">select</span> ASCII<span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 50</span>
<span class="token keyword">select</span> ASCII<span class="token punctuation">(</span><span class="token string">&#39;dx&#39;</span><span class="token punctuation">)</span> <span class="token comment">-- 100</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="合并多列为一列" tabindex="-1"><a class="header-anchor" href="#合并多列为一列" aria-hidden="true">#</a> 合并多列为一列</h4><blockquote><p>CONCAT 语法</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 如果任何参数是NULL，返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。</span>
<span class="token comment">-- CONCAT(列名1,&quot;/&quot;,列名2,&quot;/&quot;,列名3) 别名</span>
CONCAT<span class="token punctuation">(</span>c1<span class="token punctuation">.</span>category_name<span class="token punctuation">,</span><span class="token string">&quot;/&quot;</span><span class="token punctuation">,</span>c2<span class="token punctuation">.</span>category_name<span class="token punctuation">,</span><span class="token string">&quot;/&quot;</span><span class="token punctuation">,</span>c3<span class="token punctuation">.</span>category_name<span class="token punctuation">)</span> cname

<span class="token comment">-- 先分组 在合并多列为一列</span>
<span class="token comment">-- GROUP_CONCAT(列名1,列名2) 别名</span>
GROUP_CONCAT<span class="token punctuation">(</span>id<span class="token punctuation">,</span>username<span class="token punctuation">)</span> cname

<span class="token comment">-- 分组后多行列值合并一行 并进行倒序</span>
<span class="token comment">-- 按 attendance_time 分组, 将每一组的 user_id 列每一行合并一行并倒序</span>
<span class="token keyword">SELECT</span>
	GROUP_CONCAT<span class="token punctuation">(</span> <span class="token string">&quot;&quot;</span><span class="token punctuation">,</span> user_id <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> user_id <span class="token keyword">DESC</span><span class="token punctuation">,</span> <span class="token string">&quot;&quot;</span> <span class="token punctuation">)</span> <span class="token keyword">AS</span> day_check_time
<span class="token keyword">FROM</span>
	<span class="token identifier"><span class="token punctuation">`</span>checking_detail<span class="token punctuation">`</span></span>
	<span class="token keyword">ORDER</span> <span class="token keyword">BY</span>
	attendance_time
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><p>案例</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> s<span class="token punctuation">.</span><span class="token operator">*</span><span class="token punctuation">,</span> CONCAT<span class="token punctuation">(</span>c1<span class="token punctuation">.</span>category_name<span class="token punctuation">,</span><span class="token string">&quot;/&quot;</span><span class="token punctuation">,</span>c2<span class="token punctuation">.</span>category_name<span class="token punctuation">,</span><span class="token string">&quot;/&quot;</span><span class="token punctuation">,</span>c3<span class="token punctuation">.</span>category_name<span class="token punctuation">)</span> cname
<span class="token keyword">from</span> spu s
<span class="token keyword">inner</span> <span class="token keyword">join</span> category c1 <span class="token keyword">on</span> s<span class="token punctuation">.</span>category_id1 <span class="token operator">=</span> c1<span class="token punctuation">.</span>category_id
<span class="token keyword">inner</span> <span class="token keyword">join</span> category c2 <span class="token keyword">on</span> s<span class="token punctuation">.</span>category_id2 <span class="token operator">=</span> c2<span class="token punctuation">.</span>category_id
<span class="token keyword">inner</span> <span class="token keyword">join</span> category c3 <span class="token keyword">on</span> s<span class="token punctuation">.</span>category_id3 <span class="token operator">=</span> c3<span class="token punctuation">.</span>category_id

<span class="token keyword">SELECT</span> GROUP_CONCAT<span class="token punctuation">(</span>id<span class="token punctuation">,</span>username<span class="token punctuation">)</span> <span class="token keyword">FROM</span> <span class="token identifier"><span class="token punctuation">`</span>rbac_user<span class="token punctuation">`</span></span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><img src="https://apaiimages.oss-cn-guangzhou.aliyuncs.com/MD/image-20220809144852195.png" alt="image-20220809144852195"></p><p><img src="https://apaiimages.oss-cn-guangzhou.aliyuncs.com/MD/image-20220819220210334.png" alt="image-20220819220210334"></p><h4 id="获取字符串长度" tabindex="-1"><a class="header-anchor" href="#获取字符串长度" aria-hidden="true">#</a> 获取字符串长度</h4><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- LENGTH(str):返回字符串str的长度。</span>
<span class="token keyword">select</span> LENGTH<span class="token punctuation">(</span>‘<span class="token keyword">text</span>’<span class="token punctuation">)</span><span class="token punctuation">;</span>  <span class="token comment">-- 4</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="截取字符串" tabindex="-1"><a class="header-anchor" href="#截取字符串" aria-hidden="true">#</a> 截取字符串</h4><div class="language-java line-numbers-mode" data-ext="java"><pre class="language-java"><code><span class="token operator">--</span> <span class="token function">LEFT</span><span class="token punctuation">(</span>str<span class="token punctuation">,</span>len<span class="token punctuation">)</span><span class="token operator">:</span>返回字符串str的最左面len个字符。
select <span class="token function">LEFT</span><span class="token punctuation">(</span>‘foobarbar’<span class="token punctuation">,</span> <span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token operator">--</span> fooba

<span class="token operator">--</span> <span class="token function">RIGHT</span><span class="token punctuation">(</span>str<span class="token punctuation">,</span>len<span class="token punctuation">)</span><span class="token operator">:</span>返回字符串str的最右面len个字符
select <span class="token function">RIGHT</span><span class="token punctuation">(</span>‘foobarbar’<span class="token punctuation">,</span> <span class="token number">4</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token operator">--</span> rbar

<span class="token operator">--</span> <span class="token function">SUBSTRING</span><span class="token punctuation">(</span>str<span class="token punctuation">,</span>pos<span class="token punctuation">)</span><span class="token operator">:</span>从字符串str的起始位置pos到最后 返回一个子串。
select <span class="token function">SUBSTRING</span><span class="token punctuation">(</span>‘<span class="token class-name">Quadratically</span>’<span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token operator">--</span> ratically

<span class="token operator">--</span> <span class="token function">TRIM</span><span class="token punctuation">(</span>str<span class="token punctuation">)</span><span class="token operator">:</span>返回字符串str，所有前缀或后缀被删除了
select <span class="token function">TRIM</span><span class="token punctuation">(</span>’ bar &#39;<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token operator">--</span> bar
            
<span class="token operator">--</span> <span class="token function">LTRIM</span><span class="token punctuation">(</span>str<span class="token punctuation">)</span><span class="token operator">:</span>返回删除了其前置空格字符的字符串str。
select <span class="token function">LTRIM</span><span class="token punctuation">(</span>’ barbar’<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token operator">--</span> <span class="token function">RTRIM</span><span class="token punctuation">(</span>str<span class="token punctuation">)</span><span class="token operator">:</span>返回删除了其拖后空格字符的字符串str。
select <span class="token function">RTRIM</span><span class="token punctuation">(</span>‘barbar ’<span class="token punctuation">)</span><span class="token punctuation">;</span>
            
<span class="token operator">--</span> <span class="token function">REPLACE</span><span class="token punctuation">(</span>str<span class="token punctuation">,</span>from_str<span class="token punctuation">,</span>to_str<span class="token punctuation">)</span><span class="token operator">:</span>返回字符串str，其字符串from_str的所有出现由字符串to_str代替
            
<span class="token operator">--</span> <span class="token function">REPEAT</span><span class="token punctuation">(</span>str<span class="token punctuation">,</span>count<span class="token punctuation">)</span><span class="token operator">:</span>返回由重复countTimes次的字符串str组成的一个字符串。如果count <span class="token operator">&lt;=</span> <span class="token number">0</span>，返回一个空字符串。如果str或count是<span class="token constant">NULL</span>，返回<span class="token constant">NULL</span>。
select <span class="token function">REPEAT</span><span class="token punctuation">(</span>‘<span class="token class-name">MySQL</span>’<span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token operator">--</span> <span class="token class-name">MySQLMySQLMySQL</span>
            
<span class="token operator">--</span> <span class="token function">REVERSE</span><span class="token punctuation">(</span>str<span class="token punctuation">)</span><span class="token operator">:</span>返回颠倒字符顺序的字符串str。
select <span class="token function">REVERSE</span><span class="token punctuation">(</span>‘abc’<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token operator">--</span> cba
            
<span class="token operator">--</span> <span class="token function">INSERT</span><span class="token punctuation">(</span>str<span class="token punctuation">,</span>pos<span class="token punctuation">,</span>len<span class="token punctuation">,</span>newstr<span class="token punctuation">)</span><span class="token operator">:</span>返回字符串str，在位置pos起始的子串且len个字符长的子串由字符串newstr代替。
select <span class="token function">INSERT</span><span class="token punctuation">(</span>‘whatareyou’<span class="token punctuation">,</span> <span class="token number">5</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">,</span> ‘is’<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token operator">--</span> whatisyou
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="判空列" tabindex="-1"><a class="header-anchor" href="#判空列" aria-hidden="true">#</a> 判空列</h3><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span>
<span class="token comment">-- thirtysum 为null 则 值为0 反之还是原来的值 as: 别名</span>
<span class="token keyword">COALESCE</span><span class="token punctuation">(</span>thirtysum<span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span> <span class="token keyword">as</span> thirtyDaysAmount<span class="token punctuation">,</span>
<span class="token keyword">from</span> stock_in_order s
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="双select查询组合成一表" tabindex="-1"><a class="header-anchor" href="#双select查询组合成一表" aria-hidden="true">#</a> 双select查询组合成一表</h3><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span>
        u<span class="token punctuation">.</span>name <span class="token keyword">as</span> unitName<span class="token punctuation">,</span> u<span class="token punctuation">.</span>id <span class="token keyword">as</span> unitId<span class="token punctuation">,</span>
        <span class="token function">sum</span><span class="token punctuation">(</span>s<span class="token punctuation">.</span>amount<span class="token punctuation">)</span> <span class="token keyword">as</span> endAmount
  <span class="token comment">-- 将查询出来的归于一张表进行左连接 使用对应的字段对接</span>
  <span class="token keyword">left</span> <span class="token keyword">join</span> <span class="token punctuation">(</span>
        <span class="token keyword">select</span> unit_id<span class="token punctuation">,</span> <span class="token function">sum</span><span class="token punctuation">(</span>amount<span class="token punctuation">)</span> thirtysum <span class="token keyword">from</span> stock_in_order 
        <span class="token keyword">where</span> audit_status <span class="token operator">&gt;</span> <span class="token number">2</span> <span class="token operator">and</span> category <span class="token operator">=</span> <span class="token number">2</span> <span class="token operator">and</span> TIMESTAMPDIFF<span class="token punctuation">(</span><span class="token keyword">DAY</span>  <span class="token punctuation">,</span>busi_date<span class="token punctuation">,</span> <span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">&amp;</span>lt<span class="token punctuation">;</span> <span class="token number">30</span> <span class="token operator">and</span> unit_id <span class="token operator">is</span> <span class="token operator">not</span> <span class="token boolean">null</span> <span class="token keyword">group</span> <span class="token keyword">by</span> unit_id
  <span class="token punctuation">)</span> <span class="token keyword">as</span> s1 <span class="token keyword">on</span> s1<span class="token punctuation">.</span>unit_id <span class="token operator">=</span> s<span class="token punctuation">.</span>unit_id
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="联合外全-查询" tabindex="-1"><a class="header-anchor" href="#联合外全-查询" aria-hidden="true">#</a> 联合外全 查询</h3><blockquote><p>Union | all 即 将多张表的数据一次性显示成一张表的数据 且列数一致</p></blockquote><p>防坑指南:</p><ul><li>列数不一致 会报错</li><li>列的数据类型 不一致不会报错 但是会导致类型不明确或者转换字符串</li><li>Union 会将行数相同的数据合并一行 去重</li><li>Union all 则不会去重 显示所有数据</li></ul><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- Union 会将行数相同的数据合并一行 去重</span>
<span class="token keyword">select</span> id<span class="token punctuation">,</span> name <span class="token keyword">from</span> a
<span class="token keyword">Union</span> 
<span class="token keyword">select</span> id<span class="token punctuation">,</span> name <span class="token keyword">from</span> b
<span class="token comment">-- Union all 则不会去重 显示所有数据</span>
<span class="token keyword">select</span> id<span class="token punctuation">,</span> name <span class="token keyword">from</span> a
<span class="token keyword">Union</span> <span class="token keyword">all</span>
<span class="token keyword">select</span> id<span class="token punctuation">,</span> name <span class="token keyword">from</span> b
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="条件判断后聚合" tabindex="-1"><a class="header-anchor" href="#条件判断后聚合" aria-hidden="true">#</a> 条件判断后聚合</h3><blockquote><p>以同列值为分组合为一条时 会导致后面对应多条语句</p><p>使用 条件判断后聚合 创建新的列 值为; 经过 if 判断的聚合函数 的唯一值</p></blockquote><p><strong>语法</strong></p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 语法一: </span>
<span class="token comment">-- 聚合函数(if(条件列名=&#39;值&#39;, 为true时的值, 为false时的值)) 列名的别名,</span>
<span class="token function">sum</span><span class="token punctuation">(</span><span class="token keyword">if</span><span class="token punctuation">(</span>b<span class="token punctuation">.</span>sub_name<span class="token operator">=</span><span class="token string">&#39;语文&#39;</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">)</span> 语文<span class="token punctuation">,</span>

<span class="token comment">-- 语法二:</span>
<span class="token comment">-- 聚合函数(case when 条件列名=&#39;值&#39; then 为true时的值 else 为false时的值 end) 列名的别名,</span>
<span class="token function">sum</span><span class="token punctuation">(</span><span class="token keyword">case</span> <span class="token keyword">when</span> b<span class="token punctuation">.</span>sub_name<span class="token operator">=</span><span class="token string">&#39;语文&#39;</span> <span class="token keyword">then</span> <span class="token number">1</span> <span class="token keyword">else</span> <span class="token number">0</span> <span class="token keyword">end</span><span class="token punctuation">)</span> 语文<span class="token punctuation">,</span>

<span class="token comment">-- 如果expression_1不为NULL，则IFNULL函数返回expression_1; 否则返回expression_2的结果。</span>
IFNULL<span class="token punctuation">(</span>expression_1<span class="token punctuation">,</span>expression_2<span class="token punctuation">)</span><span class="token punctuation">;</span>

</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p><strong>案例</strong></p><blockquote><p>解释: 如数学 --&gt; 件为真(数学) 则值为数学分数 在判断为假(语文-外语) 值为 0 最后将使用的值进行聚合为一个数值 然后再取列名</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">SELECT</span> any_value<span class="token punctuation">(</span>t<span class="token punctuation">.</span>stu_id<span class="token punctuation">)</span> id<span class="token punctuation">,</span>t<span class="token punctuation">.</span>stu_name <span class="token keyword">as</span> <span class="token string">&#39;姓名&#39;</span><span class="token punctuation">,</span> any_value<span class="token punctuation">(</span>g<span class="token punctuation">.</span>gra_name<span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token string">&#39;班级&#39;</span><span class="token punctuation">,</span>
<span class="token function">sum</span><span class="token punctuation">(</span><span class="token keyword">if</span><span class="token punctuation">(</span>b<span class="token punctuation">.</span>sub_name<span class="token operator">=</span><span class="token string">&#39;语文&#39;</span><span class="token punctuation">,</span> s<span class="token punctuation">.</span>sco_grade<span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">)</span> 语文<span class="token punctuation">,</span>
<span class="token function">sum</span><span class="token punctuation">(</span><span class="token keyword">case</span> <span class="token keyword">when</span> b<span class="token punctuation">.</span>sub_name<span class="token operator">=</span><span class="token string">&#39;数学&#39;</span> <span class="token keyword">then</span> s<span class="token punctuation">.</span>sco_grade <span class="token keyword">else</span> <span class="token number">0</span> <span class="token keyword">end</span><span class="token punctuation">)</span> 数学<span class="token punctuation">,</span>
<span class="token function">sum</span><span class="token punctuation">(</span><span class="token keyword">case</span> <span class="token keyword">when</span> b<span class="token punctuation">.</span>sub_name<span class="token operator">=</span><span class="token string">&#39;外语&#39;</span> <span class="token keyword">then</span> s<span class="token punctuation">.</span>sco_grade <span class="token keyword">else</span> <span class="token number">0</span> <span class="token keyword">end</span><span class="token punctuation">)</span> 外语
<span class="token keyword">FROM</span> stu_student t
<span class="token keyword">left</span> <span class="token keyword">JOIN</span> stu_score s <span class="token keyword">on</span> s<span class="token punctuation">.</span>sco_stuid <span class="token operator">=</span> t<span class="token punctuation">.</span>stu_id 
<span class="token keyword">left</span> <span class="token keyword">join</span> stu_subject b <span class="token keyword">on</span> s<span class="token punctuation">.</span>sco_subid <span class="token operator">=</span> b<span class="token punctuation">.</span>sub_id
<span class="token keyword">left</span> <span class="token keyword">join</span> stu_grade g <span class="token keyword">on</span> g<span class="token punctuation">.</span>gra_id <span class="token operator">=</span> t<span class="token punctuation">.</span>stu_classid
<span class="token keyword">where</span> g<span class="token punctuation">.</span>gra_id <span class="token operator">=</span> <span class="token number">2</span>
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> t<span class="token punctuation">.</span>stu_name
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="mysql-技巧" tabindex="-1"><a class="header-anchor" href="#mysql-技巧" aria-hidden="true">#</a> MySql 技巧</h2><h3 id="时间字段自动添加-更新" tabindex="-1"><a class="header-anchor" href="#时间字段自动添加-更新" aria-hidden="true">#</a> 时间字段自动添加|更新</h3><p>详见: <a href="https://blog.csdn.net/qq_41942909/article/details/80749766" target="_blank" rel="noopener noreferrer">MySql数据库插入一条数据时，create_time字段自动添加为当前时间<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a></p><blockquote><p>自动添加插入数据的时间</p></blockquote><ol><li>设置字段类型为datetime。(不建议使用<a href="https://so.csdn.net/so/search?q=timestamp&amp;spm=1001.2101.3001.7020" target="_blank" rel="noopener noreferrer">timestamp<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a>类型)</li><li>默认值填写为 CURRENT_TIMESTAMP （需要自己手动填写，注意单词拼写正确）</li><li>这样就达成了我们的目的，插入一条数据的时候，create_time字段会自动添加内容为插入数据的时间</li></ol><blockquote><p>自动添加修改数据的时间</p></blockquote><ol><li>设置字段类型为datetime。(不建议使用<a href="https://so.csdn.net/so/search?q=timestamp&amp;spm=1001.2101.3001.7020" target="_blank" rel="noopener noreferrer">timestamp<span><svg class="external-link-icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><span class="external-link-icon-sr-only">open in new window</span></span></a>类型)</li><li>默认值填写为 CURRENT_TIMESTAMP （需要自己手动填写，注意单词拼写正确）</li><li><strong>需要勾选默认值下方的 根据当前时间戳更新</strong>，修改一条数据的时候，create_time字段会自动添加内容为修改数据的时间</li></ol><p><img src="https://apaiimages.oss-cn-guangzhou.aliyuncs.com/MD/nhifr8-0.png" alt="image-20221007121329040"></p><h2 id="mysql-原理" tabindex="-1"><a class="header-anchor" href="#mysql-原理" aria-hidden="true">#</a> MySql 原理</h2><h3 id="mysql-树原理" tabindex="-1"><a class="header-anchor" href="#mysql-树原理" aria-hidden="true">#</a> MySql 树原理</h3><blockquote><p>MySql 使用的树有: B+ 树 | B 树 | Hash | 红黑树 | 二叉树 [InnoDB 使用 B+ 树]</p></blockquote><h4 id="二叉查找树" tabindex="-1"><a class="header-anchor" href="#二叉查找树" aria-hidden="true">#</a> 二叉查找树</h4><p>二叉树具有以下性质：<strong>左子树的键值小于根的键值，右子树的键值大于根的键值。</strong></p><h4 id="平衡二叉树-avl-tree" tabindex="-1"><a class="header-anchor" href="#平衡二叉树-avl-tree" aria-hidden="true">#</a> 平衡二叉树（AVL Tree）</h4><p>平衡二叉树（AVL树）<strong>是一棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树</strong>。也就是说在符合二叉查找树的条件下，它还满足任何节点的两个子树的高度最大差为1。</p><blockquote><p>AVL树失去平衡之后，可以通过旋转使其恢复平衡。下面分别介绍四种失去平衡的情况下对应的旋转方法。</p></blockquote><p>这四种失去平衡的姿态都有各自的定义：</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- LL：LeftLeft，也称“左左”。插入或删除一个节点后，根节点的左孩子（Left Child）的左孩子（Left Child）还有非空节点，导致根节点的左子树高度比右子树高度高2，AVL树失去平衡。</span>
<span class="token comment">-- RR：RightRight，也称“右右”。插入或删除一个节点后，根节点的右孩子（Right Child）的右孩子（Right Child）还有非空节点，导致根节点的右子树高度比左子树高度高2，AVL树失去平衡。</span>
<span class="token comment">-- LR：LeftRight，也称“左右”。插入或删除一个节点后，根节点的左孩子（Left Child）的右孩子（Right Child）还有非空节点，导致根节点的左子树高度比右子树高度高2，AVL树失去平衡。</span>
<span class="token comment">-- RL：RightLeft，也称“右左”。插入或删除一个节点后，根节点的右孩子（Right Child）的左孩子（Left Child）还有非空节点，导致根节点的右子树高度比左子树高度高2，AVL树失去平衡。</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>LL===&gt;右单旋</p><p>RR===&gt;左单旋</p><p>LR===&gt;先左旋，后右旋</p><p>RL===&gt;先右旋，后左旋</p><h4 id="b-tree-平衡多路查找树" tabindex="-1"><a class="header-anchor" href="#b-tree-平衡多路查找树" aria-hidden="true">#</a> B-Tree（平衡多路查找树）</h4><p>B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。</p><p><img src="https://apaiimages.oss-cn-guangzhou.aliyuncs.com/MD/2019090109300123.png" alt="在这里插入图片描述"></p><h4 id="b-tree" tabindex="-1"><a class="header-anchor" href="#b-tree" aria-hidden="true">#</a> B+Tree</h4><p>B+Tree是在B-Tree基础上的一种优化，InnoDB存储引擎就是用B+Tree实现其索引结构。</p><p>B+树的特性：</p><ul><li>所有的<strong>叶子结点中包含了全部关键字的信息</strong>，<strong>非叶子节点只存储键值信息</strong>，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接，所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。 (而B树的非终节点也包含需要查找的有效信息)</li><li>所有叶子节点之间都有一个链指针。</li><li>数据记录都存放在叶子节点中。</li></ul><p>由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储3个键值及指针信息，则变成B+Tree后其结构如下图所示： <img src="https://apaiimages.oss-cn-guangzhou.aliyuncs.com/MD/20190901093019761.png" alt="在这里插入图片描述"></p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 数据库中的B+Tree索引可以分为**聚集索引**（clustered index）和辅助索引（secondary index）。上面的B+Tree示例图在数据库中的实现即为聚集索引，</span>
<span class="token comment">-- 聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，</span>
<span class="token comment">-- 而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="表-设计的三范式" tabindex="-1"><a class="header-anchor" href="#表-设计的三范式" aria-hidden="true">#</a> 表 设计的三范式</h3><blockquote><p>第一范式: 原子性</p></blockquote><ul><li><p>表的每一列必须为 原子性 不可在分隔</p></li><li><p>如果该列可在分割 则必须分开为多列</p></li></ul><blockquote><p>第二范式: 字段与主键的完全完全依赖</p></blockquote><ul><li>表的其他列 必须和主键ID 完全依赖</li><li>一张表不能出现 其他列 和 多个列或者主键 部分依赖 直白点就是 一张表 根据id查询 也可以 根据其他列查询到 (该列和主键一样唯一值)</li><li>如果出现 部分依赖 则必须拆分表 为多个对应的表</li></ul><blockquote><p>第三范式: 不能有传递依赖</p></blockquote><ul><li>列A 依赖于 列B | 列B 依赖于 列C</li><li>如果出现 传递依赖 显得列A多余 则必须拆分表 为多个对应的表</li></ul><h3 id="mysql-事务" tabindex="-1"><a class="header-anchor" href="#mysql-事务" aria-hidden="true">#</a> MySql 事务</h3><p>事务是一系列的数据库操作，是数据库应用的基本单位。</p><blockquote><p>MySQL 中只有 InnoDB 引擎支持事务，它的四个特性:</p></blockquote><ul><li><p>原子性（Atomic），要么全部执行，要么全部不执行；事务的原子性是通过 <strong>undolog 回滚日志</strong> 来实现的，记录更新的相反操作</p></li><li><p>一致性（Consistency），事务的执行使得数据库从一种正确状态转化为另一种正确状态；通过原子性，持久性，隔离性来实现的</p></li><li><p>隔离性（Isolation），在事务正确提交之前，不允许把该事务对数据的任何改变提供给其他事务；通过读写锁+MVCC来实现的</p></li><li><p>持久性（Durability），事务提交后，其结果永久保存在数据库中。通过 <strong>redo log 日志</strong> 来实现的</p></li></ul><blockquote><p>实现原理</p></blockquote><p><strong>原子性原理：</strong></p><p>从8.0.3开始，默认undo tablespace的个数从0调整为2 也就是在8.0版本中，独立undo tablespace被默认打开</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>通过undolog回滚日志来实现的，undo log是一种用于撤销回退的日志， 可以理解为当delete一条记录时， undo log中会记录一条对应的insert记录；当insert一条记录时， undo log中会一条对应的delete记录；当update一条记录时， undo log中记录一条对应相反的update记录
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p><strong>持久性原理：</strong></p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>Redo Log(重做日志)记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化到磁盘。当系统崩溃时，虽然数据没有持久化，但是Redo Log经持久化。系统可以根据Redo Log的内容刷新到磁盘
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h3 id="mysql-事务隔离级别" tabindex="-1"><a class="header-anchor" href="#mysql-事务隔离级别" aria-hidden="true">#</a> MySql 事务隔离级别</h3><h4 id="开启和关闭事务" tabindex="-1"><a class="header-anchor" href="#开启和关闭事务" aria-hidden="true">#</a> 开启和关闭事务</h4><blockquote><p>默认情况下，单独的一条sql就是一个事务，所谓默认情况指的是你没有手动去开启事务</p></blockquote><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>## 查看自动提交状态
select @@autocommit;
## 值为0： 关闭自动提交   值为1：开启自动提交
set autocommit = 0|1; 
关闭自动提交后，从下一条sql语句开始则开启新事务，需要使用commit或rollback语句结束该事务
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="隔离级别" tabindex="-1"><a class="header-anchor" href="#隔离级别" aria-hidden="true">#</a> 隔离级别</h4><p>MySQL是一个服务器／客户端架构的软件，对于同一个服务器来说，可以有若干个客户端与之连接，每个客户端与服务器连接上之后，就可以称之为一个会话（Session）</p><div class="language-java line-numbers-mode" data-ext="java"><pre class="language-java"><code>我们可以同时在不同的会话里输入各种语句，这些语句可以作为事务的一部分进行处理。不同的会话可以同时发送请求，也就是说服务器可能同时在处理多个事务，这样子就会导致不同的事务可能同时访问到相同的记录。我们前边说过事务有一个特性称之为隔离性，理论上在某个事务对某个数据进行访问时，其他事务应该进行排队，当该事务提交之后，其他事务才可以继续访问这个数据。但是这样子的话对性能影响太大，所以设计数据库的大叔提出了各种隔离级别，来最大限度的提升系统并发处理事务的能力，但是这也是以牺牲一定的隔离性来达到的
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>## 查看当前数据库的隔离级别
mysql&gt; SELECT @@tx_isolation    ## mysql默认是可重复读
## 修改当前数据库的隔离级别
mysql&gt; set global transaction isolation level read uncommitted;   ## 这是设置的读未提交级别

注意：设置后要重启客户端
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><p>读未提交 READ UNCOMMITTED</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 如果一个事务读到了另一个未提交事务修改过的数据，那么这种`隔离级别`就称之为`未提交读`（英文名：`READ UNCOMMITTED`），</span>
<span class="token comment">-- 事务A对数据做的修改，即使没有提交，对于事务B来说也是可见的，这种问题叫脏读，这是隔离程度较低的一种隔离级别，在实际运用中会引起很多问题，因此一般不常用，它不能解决脏读，可重复读，幻读的问题</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><p>读已提交 READ COMMITTED</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 如果一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那么这种`隔离级别`就称之为`已提交读`（英文名：`READ COMMITTED`），</span>
<span class="token comment">-- 在此隔离级别下，不会出现脏读的问题。事务A对数据做的修改，提交之后会对事务B可见。**它能解决脏读问题，不能解决可重复读和幻读问题**</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><p>可重复读 REPEATABLE READ</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 在该业务场景中，事务B只能读到事务A已经提交的事务修改过的数据，但是第一次读过某条记录后，即使其他事务修改了该记录的值并且提交，该事务之后再读该条记录时，读到的仍是第一次读到的值，而不是每次都读到不同的数据。那么这种隔离级别就称之为可重复读（英文名：REPEATABLE READ） </span>
<span class="token comment">-- 可重复读解决了脏读和不可重复读的问题，但是不能解决幻读问题</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><p>串行化 SERIALIZABLE</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 以上3种隔离级别都允许对同一条记录进行`读-读`、`读-写`、`写-读`的并发操作，</span>
<span class="token comment">-- 如果我们不允许`读-写`、`写-读`的并发操作，可以使用`SERIALIZABLE`隔离级别，即事务之间的执行是串行的，</span>
<span class="token comment">-- 当一个事务在操作的时候，另外的事务就只能等，必须等到该事务提交或者回滚，其它的事务才能继续操作，serializable隔离级别的多个事务不可以同时对同一张表修改！</span>

<span class="token comment">-- 串行化，能解决脏读，可重复读，幻读问题，但是效率非常慢，因为事务执行是串行执行的。</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><p>幻读</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 事务B按照一定条件进行数据读取， 期间事务A插入了相同搜索条件的新数据，事务A查询的时候，还是看不到B插入的新数据，但是其实数据库是有的，即使A事务提交了，B也发现不了，这个时候B也插入一条相同的数据，就会报错。这种情况称为幻读</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h3 id="mysql-锁" tabindex="-1"><a class="header-anchor" href="#mysql-锁" aria-hidden="true">#</a> MySql 锁</h3><h4 id="加锁的目的是什么" tabindex="-1"><a class="header-anchor" href="#加锁的目的是什么" aria-hidden="true">#</a> 加锁的目的是什么</h4><blockquote><p>数据加锁是为了解决事务的隔离性问题，让事务之间相互不影响，每个事务进行操作的时候都必须先对数据加上一把锁，防止其他事务同时操作数据。</p></blockquote><h4 id="锁是基于什么实现的" tabindex="-1"><a class="header-anchor" href="#锁是基于什么实现的" aria-hidden="true">#</a> 锁是基于什么实现的</h4><ul><li><p>数据库里面的锁是基于索引实现的，在Innodb中我们的锁都是作用在索引上面的，</p></li><li><p>当我们的SQL命中索引时，那么锁住的就是命中条件内的索引节点(行锁)，</p></li><li><p>如果没有命中索引的话，那我们锁的就是整个索引树（表锁），</p></li><li><p>完全取决于你的条件是否有命中到对应的索引节点。</p></li></ul><h4 id="锁的分类" tabindex="-1"><a class="header-anchor" href="#锁的分类" aria-hidden="true">#</a> 锁的分类</h4><blockquote><p>数据库里有的锁有很多种，为了方面理解，所以我根据其相关性&quot;人为&quot;的对锁进行了一个分类，分别如下</p></blockquote><ul><li>基于锁的属性分类：共享锁、排他锁。</li><li>基于锁的粒度分类：表锁、行锁、记录锁、间隙锁、临键锁。</li><li>基于锁的状态分类：意向共享锁、意向排它锁</li></ul><h4 id="属性锁" tabindex="-1"><a class="header-anchor" href="#属性锁" aria-hidden="true">#</a> 属性锁</h4><h5 id="共享锁-share-lock" tabindex="-1"><a class="header-anchor" href="#共享锁-share-lock" aria-hidden="true">#</a> 共享锁（Share Lock）</h5><ul><li>表级共享锁又称读锁，简称S锁，</li></ul><ul><li>在一个事务访问某张表的时候，允许另一个事务对同一张表进行<!---->。</li><li>其他的操作(增删改 排它锁)则进行等待阻塞 当查询执行完在进行执行</li></ul><ul><li>加锁操作一定要在事务上进行</li></ul><blockquote><p><strong>对读取的记录加<!----></strong></p></blockquote><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>SELECT .... LOCK IN SHARE MODE;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p><img src="https://apaiimages.oss-cn-guangzhou.aliyuncs.com/MD/image-20220530152035051.png" alt="image-20220530152035051"></p><h5 id="排它锁-exclusive-lock" tabindex="-1"><a class="header-anchor" href="#排它锁-exclusive-lock" aria-hidden="true">#</a> 排它锁（Exclusive Lock）</h5><ul><li><p>行级排它锁又称为写锁，简称X锁，</p></li><li><p>在一个事务修改（insert/update/delete）某张表的某条数据时，不允许另一个事务修改<!----></p></li><li><p>其他的事务 [共享锁 排它锁] 都必须等待阻塞</p></li></ul><blockquote><p><strong>对读取的记录加<!----></strong></p></blockquote><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>SELECT .... FOR UPDATE;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p><img src="https://apaiimages.oss-cn-guangzhou.aliyuncs.com/MD/image-20220530171315783.png" alt="image-20220530171315783"></p><h4 id="粒度锁" tabindex="-1"><a class="header-anchor" href="#粒度锁" aria-hidden="true">#</a> 粒度锁</h4><h5 id="表锁" tabindex="-1"><a class="header-anchor" href="#表锁" aria-hidden="true">#</a> 表锁</h5><blockquote><p>特点： 粒度大，<strong>偏向myisam引擎</strong>，无死锁，并发度最低</p></blockquote><ul><li>表锁是指上锁的时候锁住的是整个表，</li><li>当下一个事务访问该表的时候，必须等前一个事务释放了锁才能进行对表进行访问；</li></ul><h5 id="行锁" tabindex="-1"><a class="header-anchor" href="#行锁" aria-hidden="true">#</a> 行锁</h5><blockquote><p>特点：粒度小，加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高；</p></blockquote><ul><li>行锁是对所有行级别锁的一个统称，比如下面说的记录锁、间隙锁、临键锁都是属于行锁，</li><li>行锁是指加锁的时候锁住的是表的某一行或多行记录，多个事务访问同一张表时，只有被锁住的记录不能访问，其他的记录可正常访问；</li></ul><p><strong>注意点:</strong> 索引失效使行锁变表锁</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token comment">-- 1. 没有命中索引</span>
<span class="token comment">-- 2. 条件的字段没有建立索引 或者 索引失效</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="行锁-细分" tabindex="-1"><a class="header-anchor" href="#行锁-细分" aria-hidden="true">#</a> 行锁 细分</h4><h5 id="记录锁-record-lock" tabindex="-1"><a class="header-anchor" href="#记录锁-record-lock" aria-hidden="true">#</a> 记录锁（Record Lock）</h5><blockquote><p>记录锁的范围只是表中的某一条记录，记录锁是说事务在加锁后锁住的只是表的某一条记录</p></blockquote><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>触发条件：精准条件命中，并且命中索引

记录锁的作用：加了记录锁之后数据可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前被其他事务读取的脏读问题。
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h5 id="间隙锁-gap-lock" tabindex="-1"><a class="header-anchor" href="#间隙锁-gap-lock" aria-hidden="true">#</a> 间隙锁（Gap Lock）</h5><p>间隙锁是在事务加锁后其锁住的是表记录的某一个区间，防止其它事务在这个区域内插入、修改、删除数据，这是为了防止出现幻读现象，当表的相邻ID之间出现空隙则会形成一个区间，即锁定的间隙为（A，B] 左开右闭</p><p>**触发条件：**范围查询，查询条件必须命中索引、间隙锁只会出现在REPEATABLE_READ（可重复读)的事务级别中</p><h5 id="临键锁-next-key-lock" tabindex="-1"><a class="header-anchor" href="#临键锁-next-key-lock" aria-hidden="true">#</a> 临键锁(Next-Key Lock)</h5><p>它是INNODB的行锁默认算法，总结来说它就是记录锁和间隙锁的组合，<strong>临键锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住，再之它会把相邻的下一个区间也会锁住</strong></p><p>**临键锁的作用：**结合记录锁和间隙锁的特性，临键锁避免了在范围查询时出现脏读、重复读、幻读问题。</p><h3 id="mysql-重要日志" tabindex="-1"><a class="header-anchor" href="#mysql-重要日志" aria-hidden="true">#</a> MySQL 重要日志</h3><h4 id="错误日志-log-error" tabindex="-1"><a class="header-anchor" href="#错误日志-log-error" aria-hidden="true">#</a> 错误日志 log_error</h4><blockquote><p>用来记录 MySQL 服务器运行过程中的错误信息，</p></blockquote><p>比如，无法加载 MySQL 数据库的数据文件，或权限不正确等都会被记录在此，还有主从复制环境下，从服务器进程的信息也会被记录进错误日志。默认情况下，错误日志是开启的，且无法被禁止。</p><p>查看错误日志文件</p><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>SHOW VARIABLES LIKE &#39;log_error&#39;;
##linux默认位置###     /var/log/mysqld.log 
##window默认位置###    C:\dev\mysql\mysql-8.0.19-winx64\data\QH-20210227YZPF.err
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>将 log_error 选项加入到 MySQL 配置文件的 [mysqld] 组中，形式如下：</p><div class="language-bash line-numbers-mode" data-ext="sh"><pre class="language-bash"><code><span class="token punctuation">[</span>mysqld<span class="token punctuation">]</span>
log-error<span class="token operator">=</span>dir/<span class="token punctuation">{</span>filename<span class="token punctuation">}</span>    <span class="token comment">###重启 MySQL 服务后，参数开始生效，可以在指定路径下看到 filename.err 的文件</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="通用日志-general-log" tabindex="-1"><a class="header-anchor" href="#通用日志-general-log" aria-hidden="true">#</a> 通用日志 general log</h4><p>查询（通用）日志 ：查询日志在 MySQL 中被称为 general log（通用日志），查询日志里的内容不要被“查询日志”误导，认为里面只存储 select 语句，其实不然，查询日志里面记录了数据库执行的所有命令，不管语句是否正确，如增删改查语句都会被记录，在并发操作非常多的场景下，查询信息会非常多，那么如果都记录下来会导致 IO 非常大，影响 MySQL 性能。因此如果不是在调试环境下，是不建议开启查询日志功能的。 查询日志的开启有助于帮助我们分析哪些语句执行密集，执行密集的 select 语句对应的数据是否能够被缓存，同时也可以帮助我们分析问题，因此，可以根据自己的实际情况来决定是否开启查询日志。 查询日志模式是关闭的，可以通过以下命令开启查询日志。</p><p>查看通用日志的位置</p><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>SHOW VARIABLES LIKE &#39;%general%&#39;;
##linux默认位置###     /var/lib/mysql/localhost.log                                 value值是off
##window默认位置###    C:\dev\mysql\mysql-8.0.19-winx64\data\QH-20210227YZPF.log    value值是off
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>默认通用日志是关闭的，可以开启</p><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>set global general_log=1        ##关闭通用日志 set global general_log=0
##重启mysql服务  service mysqld restart
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="慢日志-slow-log" tabindex="-1"><a class="header-anchor" href="#慢日志-slow-log" aria-hidden="true">#</a> 慢日志 slow log</h4><p>慢日志 slow log：慢查询会导致 CPU、内存消耗过高，当数据库遇到性能瓶颈时，大部分时间都是由于慢查询导致的，慢查询导致IO阻塞，开启慢查询日志，可以让 MySQL 记录下查询超过指定时间的语句，之后运维人员通过定位分析，能够很好的优化数据库性能。默认情况下，慢查询日志是不开启的，只有手动开启了，慢查询才会被记录到慢查询日志中。</p><p>查看慢日志的位置</p><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>mysql&gt; SHOW VARIABLES LIKE &#39;slow_query%&#39;;
##linux默认位置###     /var/lib/mysql/localhost-slow.log                                value值是off
##window默认位置###   C:\dev\mysql\mysql-8.0.19-winx64\data\QH-20210227YZPF-slow.log    value值是off

##查询超过多少秒才记录
mysql&gt; SHOW VARIABLES LIKE &#39;long_query_time&#39;;   ###时间以秒为单位
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>启动和设置慢查询日志</p><p>可以通过 log-slow-queries 选项开启慢查询日志。通过 long_query_time 选项来设置时间值，时间以秒为单位</p><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>vim /etc/my.cnf
[mysqld]
log-slow-queries=/tmp/mysql-slow.log
long_query_time=n
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>还可以通过以下命令启动慢查询日志、设置指定时间：</p><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>SET GLOBAL slow_query_log=ON/OFF;
#SET GLOBAL slow_query_log_file = /var/log/slow-query.log ## 慢查询日志存放目录
SET GLOBAL long_query_time=5;  #设置5秒
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>测试：</p><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>select sleep(10);  #查询10秒
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h4 id="回滚日志-undo-log" tabindex="-1"><a class="header-anchor" href="#回滚日志-undo-log" aria-hidden="true">#</a> 回滚日志 undo log</h4><blockquote><p>事务原子性（Atomic），要么全部执行，要么全部不执行；事务的原子性是通过 <strong>undolog 回滚日志</strong> 来实现的，记录更新的相反操作</p></blockquote><p>undo log（回滚日志） ：用于存储日志被修改前的值，从而保证如果修改出现异常，可以使用 undo log 日志来实现回滚操作。 undo log 和 redo log 记录物理日志不一样，它是逻辑日志，可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录，当执行 rollback 时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚。</p><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>mysql&gt; show variables like &#39;%innodb_undo%&#39;;
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><h4 id="重做日志-redo-log" tabindex="-1"><a class="header-anchor" href="#重做日志-redo-log" aria-hidden="true">#</a> 重做日志 redo log</h4><blockquote><p>事务的持久性（Durability），事务提交后，其结果永久保存在数据库中。通过 <strong>redo log 日志</strong> 来实现的</p></blockquote><p>redo log（重做日志） ：在事务频繁提交中，为了避免每一次提交都要往磁盘写， 造成IO性能的问题，MySQL 采用了这样一种缓存机制，先将数据写入内存中，再批量把内存中的数据统一刷回磁盘。为了避免将数据刷回磁盘过程中，因为掉电或系统故障带来的数据丢失问题，InnoDB 采用 redo log 来解决此问题。</p><h4 id="二进制日志-bin-log" tabindex="-1"><a class="header-anchor" href="#二进制日志-bin-log" aria-hidden="true">#</a> 二进制日志 bin log</h4><p>bin log（二进制日志） ：是一个二进制文件，主要记录所有数据库表结构变更，比如，CREATE、ALTER TABLE 等，以及表数据修改，比如，INSERT、UPDATE、DELETE 的所有操作，bin log 中记录了对 MySQL 数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其他额外信息，但是它不记录 SELECT、SHOW 等那些不修改数据的 SQL 语句。</p><p>binlog 的作用如下： •恢复（recovery）：某些数据的恢复需要二进制日志。</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>比如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行 point-in-time 的恢复； 
复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的 MySQL 数据库（一般称为 slave 或者 standby）与一台 MySQL 数据库（一般称为 master 或者 primary）进行实时同步； binlog 默认是关闭状态，可以在 MySQL 配置文件（my.cnf）中通过配置参数 
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div></div></div><div class="language-mysql line-numbers-mode" data-ext="mysql"><pre class="language-mysql"><code>show variables like &#39;%log_bin%&#39;;  ##查看binlog日志情况
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>修改mysql的etc下的my.cnf文件</p><div class="language-bash line-numbers-mode" data-ext="sh"><pre class="language-bash"><code><span class="token punctuation">[</span>mysqld<span class="token punctuation">]</span>
<span class="token assign-left variable">log_bin</span><span class="token operator">=</span>ON      <span class="token comment">#打开binlog日志</span>
<span class="token comment">##重启服务</span>
<span class="token function">service</span> mysqld restart
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><p>重启mysql服务之后，在/var/lib/mysql/目录下会看到， ON.000001文件，这是binlog日志的基本文件名，ON.index指定的是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录</p></blockquote><h3 id="mvcc实现原理" tabindex="-1"><a class="header-anchor" href="#mvcc实现原理" aria-hidden="true">#</a> MVCC实现原理</h3><blockquote><p>MVCC查询的工作流程</p></blockquote><h4 id="查询主键索引" tabindex="-1"><a class="header-anchor" href="#查询主键索引" aria-hidden="true">#</a> 查询主键索引</h4><ul><li>生成Read View读视图</li><li>通过主键查找记录，根据记录里的DB_TRX_ID与Read View读视图进行可见性判断</li><li>配合DB_ROLL_PTR回滚指针和undo log来找到当前事务可见的数据记录</li></ul><h4 id="查询二级索引" tabindex="-1"><a class="header-anchor" href="#查询二级索引" aria-hidden="true">#</a> 查询二级索引</h4><ul><li>生成Read View读视图</li><li>比较读视图的up_limit_id与MAX_TRX_ID大小</li><li>如果MAX_TRX_ID <strong>小于</strong> 本次Read View的up_limit_id，则全部可见，过滤记录中的有效记录</li><li>否则，无法通过二级索引判断可见性，需要一次遍历每条记录，反查到聚簇索引记录，通过聚簇索引记录来判断可见性</li></ul><h4 id="mvcc与隔离级别" tabindex="-1"><a class="header-anchor" href="#mvcc与隔离级别" aria-hidden="true">#</a> MVCC与隔离级别</h4><p>MVCC 只在 <strong>Read Commited 和 Repeatable Read</strong> 两种隔离级别下工作。</p><p>1.在RC隔离级别下，是<strong>每个快照读都会生成并获取最新的Read View</strong>；这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因</p><p>2.在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View, 之后的快照读获取的都是同一个Read View。最后返回符合规</p><p>总之，InnoDB 实现MVCC，是通过<code>Read View+ Undo Log</code> 实现的，Undo Log 保存了历史快照，Read View可见性规则帮助判断当前版本的数据是否可见。</p><h2 id="mysql-补充" tabindex="-1"><a class="header-anchor" href="#mysql-补充" aria-hidden="true">#</a> MySql 补充</h2><p>参考: https://blog.csdn.net/luostudent/article/details/127011118</p><h3 id="数据表" tabindex="-1"><a class="header-anchor" href="#数据表" aria-hidden="true">#</a> 数据表:</h3><blockquote><p>表 | 必备字段:</p></blockquote><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span> <span class="token keyword">bigint</span><span class="token punctuation">(</span><span class="token number">18</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">COMMENT</span> <span class="token string">&#39;主键id&#39;</span><span class="token punctuation">,</span>

<span class="token identifier"><span class="token punctuation">`</span>creator_id<span class="token punctuation">`</span></span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">32</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">&#39;&#39;</span> 表必须包<span class="token keyword">COMMENT</span> <span class="token string">&#39;创建人id&#39;</span><span class="token punctuation">,</span>
<span class="token identifier"><span class="token punctuation">`</span>modifier_id<span class="token punctuation">`</span></span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">32</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">&#39;&#39;</span> <span class="token keyword">COMMENT</span> <span class="token string">&#39;修改人id&#39;</span><span class="token punctuation">,</span>
<span class="token identifier"><span class="token punctuation">`</span>create_time<span class="token punctuation">`</span></span> <span class="token keyword">datetime</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">COMMENT</span> <span class="token string">&#39;创建时间&#39;</span><span class="token punctuation">,</span>
<span class="token identifier"><span class="token punctuation">`</span>modify_time<span class="token punctuation">`</span></span> <span class="token keyword">datetime</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">COMMENT</span> <span class="token string">&#39;更新时间&#39;</span><span class="token punctuation">,</span>
<span class="token identifier"><span class="token punctuation">`</span>is_delete<span class="token punctuation">`</span></span> <span class="token keyword">tinyint</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">&#39;0&#39;</span> <span class="token keyword">COMMENT</span> <span class="token string">&#39;删除标识：0未删除1已删除&#39;</span><span class="token punctuation">,</span>
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote><p>说明：</p></blockquote><ul><li>主键id：独立的自增的bigint，与业务无关</li><li>创建人id：记录创建人</li><li>修改人id：记录修改人</li><li>创建时间：程序维护、不修改，建索引</li><li>更新时间：数据维护 删除标识：软删除</li></ul><blockquote><p>注意事项:</p></blockquote><p><strong>1.主键使用自增 int/bigint</strong></p><p>主键id：独立的自增的bigint，与业务无关，禁止修改</p><ul><li>避免插入数据造成数据分页</li><li>提高二级索引的性能，二级索引包含主键</li><li>大表一定要bigint，防止int不够用（大约21亿左右），溢出后会导致报错。</li></ul><p><strong>2.禁止使用 default NULL</strong></p><p>所有字段均定义为NOT NULL：</p><ul><li>字符为default ‘’</li><li>数值为default 0</li><li>时间为default 0000-00-00 00:00:00</li></ul><h3 id="sql规范" tabindex="-1"><a class="header-anchor" href="#sql规范" aria-hidden="true">#</a> SQL规范：</h3><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>访问量很大的SQL不要在数据库里做排序

禁止在DB里排序，请在web server上排序（对并发量特别大的SQL），web server有上百台，而db仅仅只有个位数的数量，排序都在db，会把db压垮。

禁止在where子句中对字段施加函数，如to_date(add_time)&gt;xxxxx, 应改为: add_time &gt;= 

unix_timestamp(date_add(str_to_date(&#39;20220227&#39;,&#39;%Y%m%d&#39;),interval - 29 day))

禁止使用%做前缀模糊查询，例如LIKE “%weibo”，无法使用索引。

禁止使用select *，兼容性差，使用更多的网络流量，不能使用覆盖索引，必须回表

禁止3个以上的表join，join的字段数据类型必须绝对一致

in列表元素不能超过1000个
</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="避坑指南" tabindex="-1"><a class="header-anchor" href="#避坑指南" aria-hidden="true">#</a> 避坑指南:</h3><h4 id="left-join-增多数据条数" tabindex="-1"><a class="header-anchor" href="#left-join-增多数据条数" aria-hidden="true">#</a> LEFT JOIN 增多数据条数</h4><blockquote><p>问题的产生: 左连接即使已左表为主,但是右表对应的关系存在重复数据则会产生多余的数据</p></blockquote><h4 id="" tabindex="-1"><a class="header-anchor" href="#" aria-hidden="true">#</a> <img src="https://apaiimages.oss-cn-guangzhou.aliyuncs.com/MD/fbmd75-0.png" alt=""></h4><blockquote><p>解决方案</p></blockquote><p>参考: https://blog.csdn.net/lxy6904/article/details/114437506</p></div></div><footer class="page-meta"><!----><div class="meta-item last-updated"><span class="xicon-container left meta-item-label"><!--[--><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 32 32" class="xicon-icon" style="width:20px;height:20px;font-size:20px;color:inherit;"><path d="M26 4h-4V2h-2v2h-8V2h-2v2H6c-1.1 0-2 .9-2 2v20c0 1.1.9 2 2 2h20c1.1 0 2-.9 2-2V6c0-1.1-.9-2-2-2zm0 22H6V12h20v14zm0-16H6V6h4v2h2V6h8v2h2V6h4v4z" fill="currentColor"></path></svg><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Last Updated 2023-07-19 09:58:39<!--]--></span></span></div></footer><nav class="page-nav"><p class="hasNext inner"><!----><span class="page-nav-item next">MySql 函数 → </span></p></nav><!----></main><!--]--><div class="page-catalog-container"><h5 class="tip">文章层级目录</h5><ul><!--[--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Java-develop/MySql.html#mysql-数据库" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="MySql_数据库"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->MySql_数据库<!--]--></span></span><!--[--><!--]--></a></li><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#数据库防坑" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="数据库防坑"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->数据库防坑<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Java-develop/MySql.html#数据库-索引" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="数据库_索引"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->数据库_索引<!--]--></span></span><!--[--><!--]--></a></li><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#索引简介" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="索引简介"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->索引简介<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#存储位置和文件结构" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="存储位置和文件结构"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->存储位置和文件结构<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#索引优势和劣势" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="索引优势和劣势"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->索引优势和劣势<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#mysql的索引文件" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="mysql的索引文件"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->mysql的索引文件<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Java-develop/MySql.html#索引-结构-语法-分类" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="索引 结构 语法 分类"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->索引 结构 语法 分类<!--]--></span></span><!--[--><!--]--></a></li><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#索引结构" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="索引结构"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->索引结构<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#索引的语法" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="索引的语法"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->索引的语法<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#索引分类" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="索引分类"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->索引分类<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#如何合理创建索引" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="如何合理创建索引"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->如何合理创建索引<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Java-develop/MySql.html#mysql-性能分析及调优" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="Mysql 性能分析及调优"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Mysql 性能分析及调优<!--]--></span></span><!--[--><!--]--></a></li><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#explain-性能分析" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="Explain 性能分析"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Explain 性能分析<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#explain-详解" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="Explain 详解"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Explain 详解<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#mysql-调优-索引优化" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="Mysql 调优-索引优化"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->Mysql 调优-索引优化<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#索引失效情况-重点" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="索引失效情况（重点）"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->索引失效情况（重点）<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#索引注意点" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="索引注意点"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->索引注意点<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Java-develop/MySql.html#mysql-表数据操作" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="MySql 表数据操作"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->MySql 表数据操作<!--]--></span></span><!--[--><!--]--></a></li><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#ddl-表-操作" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="DDL: 表 操作"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->DDL: 表 操作<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#表字段-约束" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="表字段 约束"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->表字段 约束<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#字段类型" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="字段类型"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->字段类型<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#dml-增删改" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="DML: 增删改"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->DML: 增删改<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#dql-查询" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="DQL: 查询"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->DQL: 查询<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Java-develop/MySql.html#特殊条件值" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="特殊条件值"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->特殊条件值<!--]--></span></span><!--[--><!--]--></a></li><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#运算符-判断" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="运算符 - 判断"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->运算符 - 判断<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#分页-分组" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="分页  -- 分组"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->分页  -- 分组<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#条件-基本聚合函数" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="条件 基本聚合函数"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->条件 基本聚合函数<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#排序" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="排序"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->排序<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Java-develop/MySql.html#mysql-特殊函数" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="MySql 特殊函数"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->MySql 特殊函数<!--]--></span></span><!--[--><!--]--></a></li><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#时间类函数" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="时间类函数"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->时间类函数<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#数学函数" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="数学函数"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->数学函数<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#字符串函数" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="字符串函数"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->字符串函数<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#判空列" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="判空列"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->判空列<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#双select查询组合成一表" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="双select查询组合成一表"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->双select查询组合成一表<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#联合外全-查询" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="联合外全 查询"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->联合外全 查询<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#条件判断后聚合" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="条件判断后聚合"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->条件判断后聚合<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Java-develop/MySql.html#mysql-技巧" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="MySql 技巧"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->MySql 技巧<!--]--></span></span><!--[--><!--]--></a></li><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#时间字段自动添加-更新" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="时间字段自动添加|更新"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->时间字段自动添加|更新<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Java-develop/MySql.html#mysql-原理" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="MySql 原理"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->MySql 原理<!--]--></span></span><!--[--><!--]--></a></li><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#mysql-树原理" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="MySql 树原理"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->MySql 树原理<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#表-设计的三范式" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="表 设计的三范式"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->表 设计的三范式<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#mysql-事务" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="MySql 事务"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->MySql 事务<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#mysql-事务隔离级别" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="MySql 事务隔离级别"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->MySql 事务隔离级别<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#mysql-锁" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="MySql 锁"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->MySql 锁<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#mysql-重要日志" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="MySQL 重要日志"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->MySQL 重要日志<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#mvcc实现原理" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="MVCC实现原理"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->MVCC实现原理<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--]--><!--[--><li class="page-catalog-menu-depth_2"><a aria-current="page" href="/docs/Java-develop/MySql.html#mysql-补充" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="MySql 补充"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->MySql 补充<!--]--></span></span><!--[--><!--]--></a></li><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#数据表" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="数据表:"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->数据表:<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#sql规范" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="SQL规范："><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->SQL规范：<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--[--><li class="page-catalog-menu-depth_3"><a aria-current="page" href="/docs/Java-develop/MySql.html#避坑指南" class="router-link-active router-link-exact-active link page-catalog-item" aria-label="避坑指南:"><!--[--><!--]--><span class="xicon-container left"><!--[--><!----><!--]--><span class="xicon-content" style="color:inherit;font-size:14px;"><!--[-->避坑指南:<!--]--></span></span><!--[--><!--]--></a></li><!--]--><!--]--><!--]--></ul></div></div></div></div><!----><!----><!--]--></div>
    <script type="module" src="/assets/app-8a43a0f0.js" defer></script>
  </body>
</html>
